populate a combo box in a subform from a text box in the main form when clicking on a control

[MrMode]

Apologies if this seems like an ignorant question, or if my grasp of MS Access is limited. I have a form with a text box containing a value I want to use to populate a combo box when I click on a control and move to another form. Essentially, how do I carry the value across and update the second form with the information from the first when clicking on a button that has limited the form records to a specific set?

Thank you in advance

 

[dhookom]

You mention "subform" in your subject line but state "move to another form" in description. Can you provide some form and control names as well as clarify the forms?

Generically you can set the RowSource property of a combo box on an open form

Dim strSQL As String
strSQL = "SELECT EmployeeID, EmployeeLastName FROM tblEmployees WHERE DeptID = " & Me.txtDeptID
Me.subformname.form.cboEmployee.RowSource = strSQL

Duane
Hook'D on Access
MS Access MVP

 

[MrMode]

I have a form [CallRecordFrm] with a text box called {Record Allocated to] on it.

I want to 'carry' that information to another form (not a sub form - sorry) called {CallRecordFrm} and populate a text box called [AgentName] or a combo box that shows that value

Hope that helps.

In the meantime, I will try playing with the code you suggested.

Thanks

 

[dhookom]

It looks like both forms are named CallRecordFrm or is this a typo? Do you only want to set the value? You might try something like:

Forms.CallRecordFrm.AgentName = Me.[Record Allocated to]

Duane
Hook'D on Access
MS Access MVP

 

[MrMode]

Typo - sorry, first form is ContactForm with [Record allocated to]field, second form is CallRecordFrm with [AgentName] field. First form is bound to ContactTbl table and CallRecordFrm is bound to table CallRecordTbl.

 

[dhookom]

Did you attempt what I suggested?

Duane
Hook'D on Access
MS Access MVP

 

[MrMode]

Will do today and let you know if I figure it out, keep getting side tracked by work colleagues.

 

[MrMode]

Hi

I have tried both suggestions but I am struggling.

Basically, I want agents to be able to add a call record after they have made contact with a prospect. They look at the prospect details on a form called ContactForm which is bound to ContactTbl table. They click on a control which takes them to a different form, filtered to show all the call records against that prospect:

Private Sub Command59_Click()
On Error GoTo Err_Command59_Click
'This code takes the user to the call records for the individual they are looking at
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "CallRecordFrm"

stLinkCriteria = "[ESPId]=" & Me.ESPID
DoCmd.OpenForm stDocName, , , stLinkCriteria

End Sub

The second form is CallRecordFrm bound to table CallRecordTbl.

Both tables have ESPID as a unique reference which is what is used to filter the recordset.

What I am trying to do is open the call records with a new record open that is pre-populated with the ESPID (which I can use to display the prospect's name etc as a concatenated field within a combobox) so that they can complete the record without having to navigate to a blank record, find the prospect in a combo box, etc.

I am sure I just being really thick, but I am stuck.

 

[MrMode]

Got it, I know my question was wolly but you put me on the right track. Thanks!

This is working for me for now.

Private Sub Command126_Click()
On Error GoTo Err_Command126_Click

DoCmd.OpenForm "CallRecordFrm", , , "[ESPID]=" & Me.ESPID
DoCmd.GoToRecord acActiveDataObject, , acNewRec
Forms.callrecordfrm.ESPID = Me.ESPID
Forms.callrecordfrm.CallBackScheduledFor = Me.AgentID

DoCmd.Close acForm, "ContactForm", acSaveYes

Exit_Command126_Click:
Exit Sub

Err_Command126_Click:
MsgBox err.Description
Resume Exit_Command126_Click

End Sub