Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    May 2012
    Posts
    13

    Unanswered: Populate Form using a Query

    I have a table named "Event". I have records in the table that I want to populate the rest of the forms unbound text fields based upon the selection of the combo box "event name" and combo box "event date".

    The table fields are:"Event Name", "Event Date","Call for Service", "POC Name", "POC Phone 1", "POC Phone 2", "Event Instructions"

    The unbound form field names are:"QEvent Name", "QEvent Date", "QCall for Service", "QPOC Name", "QPOC Phone 1", "QPOC Phone 2", "QEvent Instructions"

    POC means Point of Contact.

    I know that I want to have this form filled in after the "event date" event procedure afterupdate but I cannot get my query to do anything but generate a new table with the correct results.

    I feel I am close and want to assign the results from the query to the unbound form field names but I don't know how to do it.

    Thank you in advance for any direction you can provide.

  2. #2
    Join Date
    Apr 2012
    Posts
    28
    Put in the After Update event something like this one: (haven't tested)


    Dim rs as ADODB.Recordset
    Set rs = New ADODB.Recordset

    rs.Open "SELECT * FROM [Event] WHERE [Event Date]=#" & QEvent_Date.Value & "#", CurrentProject.Connection

    If Not rs.EOF Then
    QEvent_Name.Value = rs![Event Name]
    QCall_for_Service.Value = rs![Call for Service]
    '...etc
    End If

    Set rs = Nothing

  3. #3
    Join Date
    May 2012
    Posts
    13
    Thank you. Here is the code I entered. I got an error message when I ran it.

    Private Sub QEvent_Date_Combo_AfterUpdate()
    DoCmd.OpenQuery "Display Call for Service", acViewNormal

    Dim rs As ADODB.RecordsetSet
    rs = New ADODB.Recordset
    rs.Open "SELECT * "
    FROM [Event]
    WHERE (((Me.[Event Name]) = [Forms]![Event]![QEvent Name Combo]) And ((Me.[Event Date]) = [Forms]![Event]![QEvent Date Combo])), CurrentProject.Connection
    IfNot rs.EOF
    QCall_for_Service.Value = rs![Call for Service]
    Me.QEvent_Type = rs![Event Type]
    Me.QPOC_Name = rs![POC Name]
    Me.QPOC_Phone_1 = rs![POC Phone 1]
    Me.QPOC_Phone_2 = rs![POC Phone 2]
    Me.QEvent_Instructions = rs![Event Instructions]
    End If
    Set rs = Nothing
    End Sub

    Error message is : User defined type not defined. Refering to the line :"Dim rs As ADODB.RecordsetSet"

    What am I doing wrong?

  4. #4
    Join Date
    May 2012
    Posts
    13
    Made a mistake in my post. Should read:

    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    rs.Open "SELECT * "
    FROM [Event]
    WHERE (((Me.[Event Name]) = [Forms]![Event]![QEvent Name Combo]) And ((Me.[Event Date]) = [Forms]![Event]![QEvent Date Combo])), CurrentProject.Connection
    IfNot rs.EOF
    QCall_for_Service.Value = rs![Call for Service]
    Me.QEvent_Type = rs![Event Type]
    Me.QPOC_Name = rs![POC Name]
    Me.QPOC_Phone_1 = rs![POC Phone 1]
    Me.QPOC_Phone_2 = rs![POC Phone 2]
    Me.QEvent_Instructions = rs![Event Instructions]
    End If
    Set rs = Nothing
    End Sub

    Error message is : User defined type not defined. Refering to the line :"Dim rs As ADODB.Recordset"


    Thanks again

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    thats alamost certainly a references problem
    in a cxode window check what referecnes (think libraries) area vailable
    I think its tools | referenees
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    May 2012
    Posts
    13
    Tried to look in Tools but the References is "greyed" out.

    I appologize but I am very very new to this so I don't understand a lot of what is suggested. I am copying and pasting the suggested solutions making as few changes as possible.

    Any other suggestions is greatly appreciated.

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by mkling View Post
    Made a mistake in my post. Should read:

    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    rs.Open "SELECT * "
    FROM [Event]
    WHERE (((Me.[Event Name]) = [Forms]![Event]![QEvent Name Combo]) And ((Me.[Event Date]) = [Forms]![Event]![QEvent Date Combo]))
    1. The way you compose the Select query is incorrect, it should be:
    Code:
    rs.Open "SELECT * " & _
            "FROM [Event] " & _
            "WHERE (((" & Me.[Event Name] & ") = " & [Forms]![Event]![QEvent Name Combo] & ") And " & _
            "((" & Me.[Event Date] & ") = " & [Forms]![Event]![QEvent Date Combo] & "))"
    2. The Reference dialog box is only available when no code runs in the database. You cannot open it if the code is simply halted (break-point or halt on error).

    3. If you cannot create a reference to the ADODB library (which would be very unusual), change the code and use the DAO library which exists by default in Access.
    Have a nice day!

  8. #8
    Join Date
    May 2012
    Posts
    13
    This is the code I have currently after I made the suggested change:

    DoCmd.OpenQuery "Display Call for Service", acViewNormal

    Dim rs As ADODB.Recordset
    Set rs = ADODB.Recordset
    rs.Open "SELECT * " & "FROM [Event] " & "WHERE (((" & Me.[Event Name] & ") = " & [Forms]![Event]![QEvent Name Combo] & ") And " & "((" & Me.[Event Date] & ") = " & [Forms]![Event]![QEvent Date Combo] & "))"
    IfNot rs.EOF
    Me.QCall_for_Service.Value = rs![Call for Service]
    Me.QEvent_Type.Value = rs![Event Type]
    Me.QPOC_Name.Value = rs![POC Name]
    Me.QPOC_Phone_1.Value = rs![POC Phone 1]
    Me.QPOC_Phone_2.Value = rs![POC Phone 2]
    Me.QEvent_Instructions.Value = rs![Event Instructions]
    End If
    Set rs = Nothing
    End Sub

    I checked the referenced and ADODB is checked. I still get the same error message.

    I am desparate for a solution to this. I thought it was a simple thing. Thank you again for your attention.

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. It should be:
    Code:
    Set rs = New ADODB.Recordset
    2. You still need to pass the connection reference to the Open method of the Recordset object:
    Code:
    rs.Open "SELECT * ... & "))", CurrentProject.Connection
    Have a nice day!

  10. #10
    Join Date
    May 2012
    Posts
    13
    I made the changes and still get the error message. Any other ideas?

    Private Sub QEvent_Date_Combo_AfterUpdate()
    DoCmd.OpenQuery "Display Call for Service", acViewNormal

    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset

    rs.Open "SELECT * " & "FROM [Event] " & "WHERE (((" & Me.[Event Name] & ") = " & [Forms]![Event]![QEvent Name Combo] & ") And " & "((" & Me.[Event Date] & ") = " & [Forms]![Event]![QEvent Date Combo] & "))", CurrentProject.Connection
    IfNot rs.EOF
    Me.QCall_for_Service.Value = rs![Call for Service]
    Me.QEvent_Type.Value = rs![Event Type]
    Me.QPOC_Name.Value = rs![POC Name]
    Me.QPOC_Phone_1.Value = rs![POC Phone 1]
    Me.QPOC_Phone_2.Value = rs![POC Phone 2]
    Me.QEvent_Instructions.Value = rs![Event Instructions]
    End If
    Set rs = Nothing
    End Sub

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What do you have in strSQL if you change the code to:
    Code:
    Dim strSQL As String
    strSQL = SELECT * FROM [Event] WHERE (((" & Me.[Event Name] & ") = " & [Forms]![Event]![QEvent Name Combo] & ") And " & "((" & Me.[Event Date] & ") = " & [Forms]![Event]![QEvent Date Combo] & "))"
    Debug.Print strSQL 
    Stop ' --> Open the Immediate window (Ctrl+G) and see what's printed there.
    rs.Open strSQL, CurrentProject.Connection
    Have a nice day!

  12. #12
    Join Date
    May 2012
    Posts
    13
    I went to references and checked the following:

    "On VBA editor menu>Tools>References>Microsoft ActiveX Data Objects 2.8 Library "

    That got rid of the original error message :User defined type not defined.

    I then tried to compile and got: Sub or Function not defined and it highlighted
    IfNot rs.EOF


    Am I getting closer?
    Thank you agin for your help and suggestions. This is kicking my but.

  13. #13
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    IfNot rs.EOF
    should be:
    Code:
    If Not rs.EOF
    or:
    Code:
    If rs.EOF = False
    Have a nice day!

  14. #14
    Join Date
    May 2012
    Posts
    13
    Definitely getting closer

    The solution was IF rs.EOF THEN

    Now it is stopping on the "where"

    rs.Open "SELECT * " & "FROM [Event] " & "WHERE (((" & Me.[Event Name] & ") = " & [Forms]![Event]![QEvent Name Combo] & ") And " & "((" & Me.[Event Date] & ") = " & [Forms]![Event]![QEvent Date Combo] & "))", CurrentProject.Connection

    The error message is:

    "syntax error(missing operator) in query expression"

  15. #15
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What do you have in the SQL statement (see my third post)?
    Have a nice day!

Posting Permissions

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