Populate Form Using Query

[mkling]

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.

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

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

[June7]

There are easier ways to do this.

1. Join tables in form RecordSource so related data will be available.

2. Have the QEvent Date Combo be a multi-column combobox. Textboxes refer to the various columns of combobox to show the related data.

However, that error usually means library reference not selected. On VBA editor menu>Tools>References>Microsoft ActiveX Data Objects 2.8 Library

[mkling]

Did this but the fields became read only. I want to populate the form so the user can edit only the fields they want then "update" the entire record with the changes. The table would store blanks if the user did not re-enter all of the fields.

[June7]

Not understanding what you are doing. I thought you were trying to display related info from a lookup table.

You are not using form bound to table (or an editable query) with controls bound to fields of RecordSource?

Want to provide project for analysis? Follow instructions at bottom of my post.

[mkling]

Attached is the db.

Thanks for the help. When the fields below date are automatically entered, I need to be able to edit them.

[June7]

You don't provide the Update query so I am not sure what tables are really involved. Are you wanting to add record to the Event table? If not, bind form to the destination table. However, all of this appears to allow repetition of data.

1. Bind all controls, including EventName and EventDate, to fields of form RecordSource.

2. Properties for EventName combobox:
RowSource: SELECT DISTINCT [Event Name], [Call for Service], [Event Type], [POC Name], [POC Phone 1], [POC Phone 2], [Event Instructions] FROM Event ORDER BY [Event Name];
BoundColumn: 1
ColumnCount: 7
ColumnWidths: 2.5";0.75";0.25";0.5";0.5";0.5";0.5"

4. Code to set values of textboxes - avoids recordset and VBA library reference, which you do need to set if using recordsets elsewhere.

Private Sub QEvent_Date_Combo_AfterUpdate()
    Me.QCall_for_Service = Me.QEvent_Name_Combo.Column(1)
    Me.QEvent_Type = Me.QEvent_Name_Combo.Column(2)
    Me.QPOC_Name = Me.QEvent_Name_Combo.Column(3)
    Me.QPOC_Phone_1 = Me.QEvent_Name_Combo.Column(4)
    Me.QPOC_Phone_2 = Me.QEvent_Name_Combo.Column(5)
    Me.QEvent_Instructions = Me.QEvent_Name_Combo.Column(6)
    Me.QCall_for_Service.SetFocus
End Sub

5. Remove the Update Record button. Setting the controls to null will just wipe out the values from record. Also, record will be committed to table when form closes or move to another record. Or have code to move to a New record: DoCmd.GoToRecord acDataForm, Me.Name, acNewRec

BTW, advise no spaces, special characters, punctuation (underscore is exception), or reserved words as names. If do must enclose in []. Better would be EventDate or Event_Date.

[mkling]

Thank you, thank you, thank you!!! It is not only poulating the form fields but I am allowed to change fields and those chages are made to the intended row.

I learned my lesson about spaces in field names.