Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2008
    Location
    NM, USA
    Posts
    97

    Question Unanswered: VBA, SQL, and variables as parameters.

    Okay folks, this is gonna be a lengthy one. I'm working on a small database and I'm trying to write the VBA code for an input form... Let me explain.

    I have essentially two tables in which I need to create new records, and two other tables from which some information needs to be selected:

    tblContractors:
    intEmp_ID
    strLastName
    strFirstName
    dteHiredate

    tblContractorInfo:
    EmployeeID
    strPositionCode
    intCCNumber

    tblPositions
    strPosCode
    strDescription

    tblLocations
    intCCnum
    strAbbreviation
    strName

    Wherein the relationships between intEmpID and EmployeeID is 1 to 1, the relationships between strPosCode and strPositionCode is 1 to many, and intCCnum and intCCnumber is also 1 to many.

    In my form, I have text boxes to obtain the input the employee ID, first and last name, and hire date. I have combo boxes to obtain the information for the position code and CC (cost center) number.

    What I want to do with this form, is in the event procedure for the ENTER button, I want to declare variables for each of these pieces of data and assign the values entered into the controls to these variables. THEN I want to pass them into my saved queries to create the new records in the tblContractors, and tblContractorInfo tables.

    The problem is, I have a pretty tenuous understanding of VBA, and a growing understanding of SQL, but I do not know the specific syntax to perform this operation, or if this is even possible. I've been searching the web and forums as well as my text books, but I can't seem to find a solution for my particular problem.

    If anyone has anything to add, I'll appreciate it. Thank you.

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Sounds like all you need to do is add the data with recordsets. Time to learn VBA a bit more
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201
    As I understand it, you have contractors who are hired and who can be assigned a position i(in the company) and a location (a particular office). You say that the relationship is 1 to 1 between tblContractors and tblContractorInfo is 1 to 1. If this is the case, why not simply combine the tables ? However, if you wish to use the second table as an employment history, then you could add start and finish dates to the second table and make it a one-to-many subtable of the first. This affects how you develop the form.
    Assuming that the relationship is 1 to 1 all you have to do is to create a form with the combined table as a source and use combo boxes for the lookup information. If the second table is to reflect a history, then it should be in a continuous subform, where the combo boxes will also reside.
    The form object contains all of the information you need to create the record, with little, if any, help from VBA. (You might use it to verify data and to ensure data is written back before closure, for instance). You will not even need to create any INSERT or UPDATE queries.

  4. #4
    Join Date
    Sep 2008
    Location
    NM, USA
    Posts
    97
    Quote Originally Posted by StarTrekker
    Sounds like all you need to do is add the data with recordsets. Time to learn VBA a bit more

    lol yeah.. I'm sorry, I ought to have included the code I'm using right now.
    as of right NOW, I have VBA code that is fine to add a record to just one table...


    Private Sub Enter_Click()

    dim db As Database
    Dim rst As Recordset

    Set db = CurrentDb
    Set rst = db.OpenRecordset("tblContractors")

    With rst

    .AddNew
    .Fields("intEmpID") = Me.Text4
    .Fields("strLastName") = Me.Text6
    .Fields("strFirstName") = Me.Text8
    .Fields("dteHireDate") = Me.Text10
    .Update

    End With

    rst.Close
    db.Close

    Me.Text4=""
    Me.Text6=""
    Me.Text8=""
    Me.Text10=""

    Set rst = Nothing
    Set db = Nothing

    End Sub


    But what I want to do is like... Assign the values from the text boxes and the combo boxes (that aren't used here in this code) into variables, then pass them as parameters into my sql...

    like...
    ...
    Dim Hired As Date
    Dim LName, Fname, Site As String
    Dim EmployeeID As Single

    EmployeeID = Me.Text4
    LName = Me.Text6
    FName = Me.Text8
    Hired = Me.Text10


    DoCmd.RunSQL "INSERT INTO tblContractors VALUES (EmployeeID,Lname,Fname,Hired)"

    ....

    EndSub

    ...or something like that.


    I am kinda having to learn all this stuff really quickly... so if you can point me in the right direction, it would be much more helpful than telling me to just learn VBA. I just need to know is this possible, and what is the syntax.

  5. #5
    Join Date
    Sep 2008
    Location
    NM, USA
    Posts
    97
    Quote Originally Posted by Jim Wright
    As I understand it, you have contractors who are hired and who can be assigned a position i(in the company) and a location (a particular office). You say that the relationship is 1 to 1 between tblContractors and tblContractorInfo is 1 to 1. If this is the case, why not simply combine the tables ? However, if you wish to use the second table as an employment history, then you could add start and finish dates to the second table and make it a one-to-many subtable of the first. This affects how you develop the form.
    Assuming that the relationship is 1 to 1 all you have to do is to create a form with the combined table as a source and use combo boxes for the lookup information. If the second table is to reflect a history, then it should be in a continuous subform, where the combo boxes will also reside.
    The form object contains all of the information you need to create the record, with little, if any, help from VBA. (You might use it to verify data and to ensure data is written back before closure, for instance). You will not even need to create any INSERT or UPDATE queries.
    There is a reason I don't combine the Contractors and Locations table. I did not go into too much detail about the design of the data model, because this is not a question about design. my question is how do you pass variables as parameters into a query. Do you know the syntax for that?

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    guessing at your datatypes, at its simplest it would be

    dim strSQL as string
    strSQL = "INSERT INTO tblContractors (EmployeeID, Lname, Fname, Hired) VALUES (" & me.Text4 & ", '" & me.Text6 & "', '" & me.Text8 & "', #" & me.Text10 & "#)
    currentdb.execute strSQL

    hope you can see how the '' and ## are used.

    observations:
    -run (do not walk) to your keyboard and kill those TextN textbox names - just because MS proposes a mindless default name does not mean that you have to accept it.
    -don't bother going textbox -> variable -> concatenation into strSQL: waste of time/space and the more typing you do the more errors you type.

    (and from my personal paranoia point of view, name the INSERT fields rather than leaving it to luck)

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Sep 2008
    Location
    NM, USA
    Posts
    97

    Thumbs up

    Quote Originally Posted by izyrider
    guessing at your datatypes, at its simplest it would be

    dim strSQL as string
    strSQL = "INSERT INTO tblContractors (EmployeeID, Lname, Fname, Hired) VALUES (" & me.Text4 & ", '" & me.Text6 & "', '" & me.Text8 & "', #" & me.Text10 & "#)
    currentdb.execute strSQL

    hope you can see how the '' and ## are used.

    observations:
    -run (do not walk) to your keyboard and kill those TextN textbox names - just because MS proposes a mindless default name does not mean that you have to accept it.
    -don't bother going textbox -> variable -> concatenation into strSQL: waste of time/space and the more typing you do the more errors you type.

    (and from my personal paranoia point of view, name the INSERT fields rather than leaving it to luck)

    izy

    Thank you so much, this is exactly what I needed to know.

    I have actually already divined a solution to my problem using record sets, but THIS is what I was intending to do. Thank you very much.

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    glad to help, but there is an ooooops:
    CORRECTION:
    strSQL = "INSERT INTO tblContractors (EmployeeID, Lname, Fname, Hired) VALUES (" & me.Text4 & ", '" & me.Text6 & "', '" & me.Text8 & "', #" & me.Text10 & "#)"

    VBA editor would have added the closing double quote on its own, but that is still not an excuse for me posting rubbish

    izy
    currently using SS 2008R2

  9. #9
    Join Date
    Sep 2008
    Location
    NM, USA
    Posts
    97
    lol that's okay. You've been more than helpful.

  10. #10
    Join Date
    Sep 2008
    Location
    NM, USA
    Posts
    97

    Lightbulb Lookin' it over...

    You know, the more I look at this... the more I can't understand why I didn't just make one table to begin with...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •