0
votes

Hopefully I can explain what I want to do well enough...here it goes...

I have a data entry form...the user will be entering employeeIDs. Once in normal operation, most people will be entering only their own EmpID, and they should know it, so this won't be a big problem 99% of the time once this DB goes live.

However, I need some temps to enter historical data from paper sheets into the DB. These people will not know anyone else's EmpID. I'd like to set the Student field's OnDblClick event in the subform's datasheet to open a small form with a combo box. The combo box has a list of all Employee Names, and is bound to the EmpID. Once this user enters the name, and selects the person, I have a button they can click to return to the datasheet.

I can use a function to launch the form, no problem there. But how do I return the EmpID to the field in the datasheet that was double clicked?

enter image description here When user double clicks in the Student field...I want the next form to appear, and then once they type in the name and select the correct person...and then click Found Them!...I need that bound value to return.

enter image description here

I'd love to say I have code to share right now...but the only code I have is to launch the look up form. I'm brain farting on how to pull the value back down.

2
Have you tried setting the value directly in the FoundThem click event? Forms!Attendance!employeeID = Me.EmpID or something like that.Andre

2 Answers

2
votes

The way to do this to launch your little dialog form as “acDialog”. This will cause the calling code to WAIT.

And then the “magic” part is when they click on “Found Them” you do NOT close the popup form, but simply set the form’s visible = false. This has the effect of the calling code that popped up this form that halted to continue (the form is kicked out of dialog mode when you do this). So now your calling code continues.

So your code will look like this:

  Dim strF       As String      ' name of popup form

  strF = "frmPopUp"

  ' open form, wait for user selection

  DoCmd.OpenForm strF, , , , , acDialog

  ' if for is NOT open, then assume user hit cancel buttion
  ' (you should likly have a cancel button on the form - that cancel buttion will
  ' execute a docmd.close

  If CurrentProject.AllForms(strF).IsLoaded = True Then
     ' grab the value of thee combbo box

     strComboBoxValue = Forms(strF)!NameOfComboBox

     DoCmd.Close acForm, strF

  End If

As noted, the code behind the Found Them button DOES NOT do a close form, but sets forms visible = false (me.Visible = false), and this trick allows the calling code to continue at which point you can examine any value on the form. Remember to then close the form after you grab the value.

1
votes

It looks like your data table is in a subform so there is a little more work but it does not have to be as complex as the above solution if you don't want it to be. @Andre451 was close but you need the extra step of identifying the form and subform. For the purpose of demonstration let's call the form Attendance and subform Entry then I'll call the second form LookUp. So the code for your double click in the subform field will of course look something like this :

Private Sub Student_DblClick(Cancel As Integer)
 DoCmd.OpenForm "LookUp" 
End Sub

You really don't need anything else fancy there. For the button on "LookUp" you will put this:

Private Sub Command2_Click()

Forms![Attendance]![Entry].Form![Student] = Forms!Lookup!Student
DoCmd.Close acForm, "LookUp"

End Sub

And that should get you what you want without any overhead or having to leave any ghosts open.