Requery data source 1

[Joallyn]

hi listers,

I have a combo box (Personnel ID) in a Main Form that asks the user if he/she wants to enter a new ID, if it is not already in the underlying Personnel table.

If so, a Child Form pops up, the user enters the new data, and then closes the window and goes back to the Main Form. The problem is that the newly-entered Personnel ID is still not recognized as a valid data record on the Main Form, so the user enters an infinite loop when he/she is again prompted to enter a new ID. This does little for data entry efficiency.

I've tried to requery the table After Update, but I don't seem to be getting anywhere. The VB editor accepts a Me!PersonnelID.requery routine in the Child Form, but what I really want to do is requery the combo box for the Main Form. I tried After Update/ Forms!frmMain!PersonnelID.requery
but get nowhere with that.

The solution seems like it should be easy. Can anyone help me out?

thanks!!

J

 

[FoxProProgrammer]

Hi J,

What do you do with the data that the user types in on the popup form? Do you save it in a table? Where does the combo box on the main form get its data from? dz
dzaccess@yahoo.com

 

[Joallyn]

Hi, sorry for being vague. Both forms access the same data: the user types in data on the Main Form that is not in the underlying Personnel table. He then enters it into the pop up form, which feeds the data directly into the Personnel table.

I hope that's clear...

thanks for replying,

J

 

[FoxProProgrammer]

Howdy J,

I'm a little confused by your terminology. ) When you say child form, do you mean that you actually have a subform, or are you referring to the pop-up form where the user enters the new data?

Are you sure that the data entered on the pop-up form is being saved in the table? You can easily verify this by opening the table to see if it's in there. If it isn't in the table, your problem is that the data isn't being saved properly. If the new data is in the table, the Requery of the combo box isn't working properly. Does the combo box get its data from a query or a table?

Best, dz
dzaccess@yahoo.com

 

[Joallyn]

Hmmm. Somewhere, there must be standard jargon for all this...

I'll try to restate it more clearly. I have 2 tables and 2 forms:

tblPersonnel
PersonnelID <----PK

tblIncident
IncidentID <---PK
PersonnelID <---FK

frmIncident
cboPersonnelID <---- if the user enters an ID that is not in
tblPersonnel, frmPersonnel pops open
to let user enter in a new person

frmPersonnel
textbox PersonnelID <-----this entry goes directly into
tblPersonnel


When the user types in an ID into frmIncident that does not exist in tblPersonnel, frmPersonnel pops up. All is well and good; the data entered into that form shows up, as it should, in tblPersonnel. The problem comes after the &quot;new&quot; person is entered into frmPersonnel and the user closes that form to return to entering data into frmIncident. At that point, the user has to reenter the PersonnelID. However, the freshly-added new person still doesn't appear in the drop-down list. Somehow, the query doesn't see the new data.

Does that make sense? I hope so. Thanks for having the patience to wade through it all....

J

 

[FoxProProgrammer]

No problem. Thanks for taking the time to explain it so well. Now I understand.

You didn't post any of your code, but I presume that you are using the Not in List event or After Update event of the combo box to check if the ID exists. At that point the code probably looks something like this:

DoCmd.OpenForm frmPersonnel,,,,,acDialog
cboPersonnelID.Requery

This *should* work. If it doesn't I would guess that you don't have the acDialog option in the OpenForm statement. What would happen in this case is that the program would continue immediately after executing the DoCmd.OpenForm statement. The acDialog option forces the program to wait for the calling form to close before continuing. You need to make sure that the Requery executes after the user has entered the new ID on frmPersonnel and closes that form. Let me know if this is the problem. If not, please post some of your code so I can get a better idea what is going on.

Thanks, dz
dzaccess@yahoo.com

 

[Joallyn]

Hi dz,

Ok, I thought that would solve the problem, but I get the following error message after I close the pop up form and return to the main one:

&quot;You must save the current field before you run the Requery action&quot;

The code is just as you thought:

On Not In List:
----------------------------------------------------------
Private Sub cboPersonnelID_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_cboPersonnelID_NotInList

Dim intAnswer As Integer

intAnswer = MsgBox(&quot;This ID is not in the Personnel ID list. Do you want to add it?&quot;, vbYesNo, vbQuestion)

If intAnswer = vbYes Then
DoCmd.RunCommand acCmdUndo
DoCmd.OpenForm &quot;frmPersonnel&quot;, acNormal, , , acFormEdit, acDialog
cboPersonnelID.Requery
Response = acDataErrContinue

Else
Reponse = acDataErrContinue
End If

Exit_cboPersonnelID_NotInList:
Exit Sub

Err_cboPersonnelID_NotInList:
MsgBox Err.Description
Resume Exit_cboPersonnelID_NotInList
End Sub
---------------------------------------------------------

Any ideas??

Again, thanks for hanging in there!

J

 

[Joallyn]

hi dz,

I added
DoCmd.RunCommand acCmdSaveRecord

before the requery line, and that seemed to do the trick. Thanks for your help!!

J