Pass data from unbound form to subform

[JayKH]

Hi, I am really struggling here any help will be much appreciated. Just for your info I am fairly new to VBA.

Here goes........

I have a data entry form (FrmTraining) and within that form a subforn (FrmAttendeeList). The subform FrmAttendeeList needs the name of the employee being entered but instead of manual entry or combo box looup, i have a button (Add Attendee) which basically opens a continuous unbound form which list details of all employees and their shift. I want the user to be able to click on any of the names in this list which will then populate the name field in the subform FrmAttendeeList and will requery this so the user can see what he has entered.

 

[delikedi]

There are several ways of doing this, and they may depend on additional info about your setup. I will just tell you the technique I frequently use.

In the click event of "Add Attendee", I would enter:

dim lngAttendeeId as long
dim strSQL as string

'open the record selector form. it will help choose the attendee
DoCmd.OpenForm , acNormal, "frmCandidateAttendees", , acFormEdit, acDialog
'the following line of code executes only after frmCandidateAttendees is made invisible
lngAttendeeId = Forms!frmCandidateAttendees.txtAttendeeID
'we captured the ID with the above line, now we can close the attendee selector form
DoCmd.Close acForm, "frmCandidateAttendees", acSaveNo

'lets insert the attendee we chose int the training table:
strSQL = "INSERT INTO tblTraining ( TrainingID, AttendeeID ) VALUES (" & lngTrainingID & ", " & lngAttendeeId & ")"
CurrentDB.Execute strSQ, dbFailOnError
'requery the form so that it displays the newly added attendee:
me.fsubYourSubformContainerName.Form.requery

The code above has several assumptions:
1. you have a "frmCandidateAttendees" with a hidden textbox named txtAttendeeID on it
2. The attendees come from a table which has a long integer primary key.
3. you have some sort of listbox or combo or subform on frmCandidateAttendees that allows you to select the attendee you want. when you select it, its PK value is reflected in txtAttendeeID.
4. To close frmCandidateAttendees, you build a "Confirm Attendee" button on it, and write the following line of code as its click procedure:

Me.Visible=False

You would also notice that the setup of tblTraining is also assumed, but I'm sure you would substitute the correct setup.

 

[JayKH]

Hi

I want to be able to add multiple attendee Id/names to my subform also I do not want the tblAttendeeList to be updated until the user clicks save on the main form.

Basically I want my unbound form to act as look up for my subform to make it easier for the user to select the attendees.

Thank you