Solved On Click Requery Not Inputting Selected Data Inside Combobox

[Methodikal]

Hi,

I have a Form with a Filter by Supervisor (named AltWorkScheduleF

The Nested Subform is for an Alternative Work Scheduler (named AltWorkScheduleSF).

They use a lot of information from multiple tables and queries.

I have everything working except... When I click on the emplyee combobox in the subform and try to select an employee it will not put that employees name into the datasheet.

All of the fields are there, the Combo Filter from the main form works. The Requery on the Employee Combobox in the subform is working and showing the needed employees.

However, when I try to click on a name to enter their record it doesn't allow me to select them and the combobox goes back to blank.

I believe it has to be a VBA issue. I have this working on another form. I have compared the two and can find no difference. Any suggestions? Here is the VBA:

Here is Both Forms VBA:

Form_AltWorkScheduleF

Option Compare Database
Option Explicit
____________________________

Private Sub Form_Load()
Me.txtDate2.SetFocus
End Sub
____________________________

Private Sub txtDate2_AfterUpdate()
Me.AltWorkScheduleSF.Requery
End Sub

Form_AltWorkScheduleSF

Option Compare Database
Option Explicit
_____________________________

Private Sub EmployeeID2_Click()
Me.EmployeeID2.Requery
End Sub
______________________________

Private Sub EmployeeID2_GotFocus()
Me.EmployeeID2.Requery
End Sub
______________________________

Private Sub Form_BeforeInsert (Cancel As Integer)
Cancel = IsDate(Me.Parent!txtDate2) = False Or (Me.Parent!cboSupervisorID3.ListIndex < 0)
End Sub

Any help would be greatly appreciated.

Thanks,

Methodikal

 

[Methodikal]

The only time it sometimes works is when I change the:

Me.EmployeeID2.Requery

To

Me.EmployeeID.Requery

However, then it gives me:

Compile error:

Method or data member not found

Which I know is because it can't find the EmployeeID2 combobox.

 

[Methodikal]

OK. For some reason I can get the combobox to show the Employee Name only but for some reason it will only accept the Employee ID into the field.

So if I change the Column Width back to 1";0"

Then it will save the EmployeeID but when I have the dropdown set to Column Width to 0";1"

It won't save the Employee Name in the Combobox to see.

Any idea how to fix this?

 

[Pat Hartman]

Are you aware that the BeforeInsert event of the form runs ONCE and only once immediately following the typing of the first character into ANY control?

Are you aware that the Requery method is NOT the correct way to save a dirty record?

Remove ALL of the Requeries except maybe the one in txtDate2_AfterUpdate(). That one makes sense if the second subform does not have Master/Child links set AND you have a where clause in the RecordSource query that positions to the correct record.
Move the code from the BeforeInsert event to the form's BeforeUpdate event.

 

[Methodikal]

Are you aware that the BeforeInsert event of the form runs ONCE and only once immediately following the typing of the first character into ANY control?

Are you aware that the Requery method is NOT the correct way to save a dirty record?

Remove ALL of the Requeries except maybe the one in txtDate2_AfterUpdate(). That one makes sense if the second subform does not have Master/Child links set AND you have a where clause in the RecordSource query that positions to the correct record.
Move the code from the BeforeInsert event to the form's BeforeUpdate event.

Thanks so much. I will try this in the morning. I'm a newbie and just trying to learn by doing.

 

[bastanu]

You should remove the requery of the combo in the Click event but you can leave it in the GotFocus event. What is the bound column of the EmployeeID2 combo? Do you have a lookup field set up in the Employee table for EmployeeID? If yes that might be the cause of your problem.

Cheers,

 

[Pat Hartman]

If the combo is being used to filter the subform, then the .requery needs to run in the click event of the combo since that is when an entry is selected. An entry is NOT selected by the GotFocus event. When the form loads, the current value of the combo is used to filter the other subform assuming the subform's RecordSource query uses the combo in its SELECT clause.

 

[bastanu]

The code as shown in the original post does NOT requery the subform itself other than the txtDate2 AfterUpdate; the intent of the GotFocus is to requery the combo box itself. The problem is with the employee combo on the subform which has nothing to do with what you mention.

Cheers,

 

[Methodikal]

Hi guys, I finally got this figured out. Like a big dumb dumb I had the EmployeeID2 field pointed to a freaking short text field versus the number field. It works now and I will keep testing around to find ways to optimize. Thank you all for your knowledge and education. I appreciate it very much.