combobox lookup question

[Annelie]

I retrieve individual payroll data via employeeselect combo box. If the
employee selected has no data in the current payroll, the previous
employee's data stays on the screen. Is there such a thing as, if no data
found in sub reports, show sub empty, or clear subreport.

 

[Sandra Daigle]

Hi Annelie,

Exactly how are you using the combo box? Hopefully it is an unbound combo
that is only used for record navigation or for linking a subform? By
unbound, I mean that the ControlSource property of the combo is empty.

It sounds as though you may be using a combo box on the EmployeeID field of
a payroll record and by changing the value you are actually changing the
employee associated with displayed payroll data. Hopefully I am wrong

Provide a bit more detail about your form and I'll be glad to try to help
you figure it out.

 

[Annelie]

Yes, my combo box is unbound. The main form's record source is the employee
list and the subreports show the selected employee's current week payroll.
The combo box code is a follows

Private Sub EmployeeSelect_AfterUpdate()
DoCmd.GoToControl "EmployeeName"
DoCmd.FindRecord Me![EmployeeSelect]
End Sub

Is there a code one can add for, if not found, clear data, rather then
keeping the previous employee's data on the screen? It's just confusing.
Annelie

Hi Annelie,

Exactly how are you using the combo box? Hopefully it is an unbound combo
that is only used for record navigation or for linking a subform? By
unbound, I mean that the ControlSource property of the combo is empty.

It sounds as though you may be using a combo box on the EmployeeID field of
a payroll record and by changing the value you are actually changing the
employee associated with displayed payroll data. Hopefully I am wrong

Provide a bit more detail about your form and I'll be glad to try to help
you figure it out.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

I retrieve individual payroll data via employeeselect combo box. If the
employee selected has no data in the current payroll, the previous
employee's data stays on the screen. Is there such a thing as, if no data
found in sub reports, show sub empty, or clear subreport.

 

[Sandra Daigle]

Hi Annelie,

Just to be clear, you are talking about the payroll data showing in a
subform (rather than a subreport) correct? If so, your subform should be
linked to the mainform with the EmployeeId field as the linking field. If
you have it linked correctly you shouldn't have to do anything, Access
should take care of it for you by showing only the Payroll records in the
subform which match the linking field on the main form.

Confirm your linking fields and then post back with what you have.

Is the Recordsource for the main form the Employee Table or is it a query
based on the Employee table. The problem you are describing might also occur
if your unbound combo is allowing you to select an employee who doesn't have
a record in the main form's recordsource. The Code in the AfterUpdate event
of the combo doesn't verify that a match is actually found and the current
record is unchanged when no match is found.

FWIW, I prefer using the recordsetclone to find and navigate to the record -
here's the sample code:

with me.recordsetclone
.findfirst "EmployeeName=""" & me.EmployeeSelect & """"
if not .nomatch then
me.bookmark = .bookmark
else
'I would not normally include this but
' it helps for debugging
msgbox "Record not found"
endif
end with


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Yes, my combo box is unbound. The main form's record source is the
employee list and the subreports show the selected employee's current
week payroll. The combo box code is a follows

Private Sub EmployeeSelect_AfterUpdate()
DoCmd.GoToControl "EmployeeName"
DoCmd.FindRecord Me![EmployeeSelect]
End Sub

Is there a code one can add for, if not found, clear data, rather then
keeping the previous employee's data on the screen? It's just confusing.
Annelie

Hi Annelie,

Exactly how are you using the combo box? Hopefully it is an unbound combo
that is only used for record navigation or for linking a subform? By
unbound, I mean that the ControlSource property of the combo is empty.

It sounds as though you may be using a combo box on the EmployeeID field
of a payroll record and by changing the value you are actually changing
the employee associated with displayed payroll data. Hopefully I am
wrong

Provide a bit more detail about your form and I'll be glad to try to help
you figure it out.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

I retrieve individual payroll data via employeeselect combo box. If the
employee selected has no data in the current payroll, the previous
employee's data stays on the screen. Is there such a thing as, if no
data found in sub reports, show sub empty, or clear subreport.

 

[Annelie]

The record source of the main form was the problem. Once I changed the
recordsource for the main form to the Employee Table instead of a query
based on the table, it is working the way I want it to. If there is no data
in the subform, it accepts the employee name and it shows empty subforms.
I also changed my After Update to your statement, just in case.
Thank you so much for your help.
Annelie

Hi Annelie,

Just to be clear, you are talking about the payroll data showing in a
subform (rather than a subreport) correct? If so, your subform should be
linked to the mainform with the EmployeeId field as the linking field. If
you have it linked correctly you shouldn't have to do anything, Access
should take care of it for you by showing only the Payroll records in the
subform which match the linking field on the main form.

Confirm your linking fields and then post back with what you have.

Is the Recordsource for the main form the Employee Table or is it a query
based on the Employee table. The problem you are describing might also occur
if your unbound combo is allowing you to select an employee who doesn't have
a record in the main form's recordsource. The Code in the AfterUpdate event
of the combo doesn't verify that a match is actually found and the current
record is unchanged when no match is found.

FWIW, I prefer using the recordsetclone to find and navigate to the record -
here's the sample code:

with me.recordsetclone
.findfirst "EmployeeName=""" & me.EmployeeSelect & """"
if not .nomatch then
me.bookmark = .bookmark
else
'I would not normally include this but
' it helps for debugging
msgbox "Record not found"
endif
end with


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Yes, my combo box is unbound. The main form's record source is the
employee list and the subreports show the selected employee's current
week payroll. The combo box code is a follows

Private Sub EmployeeSelect_AfterUpdate()
DoCmd.GoToControl "EmployeeName"
DoCmd.FindRecord Me![EmployeeSelect]
End Sub

Is there a code one can add for, if not found, clear data, rather then
keeping the previous employee's data on the screen? It's just confusing.
Annelie

Hi Annelie,

Exactly how are you using the combo box? Hopefully it is an unbound combo
that is only used for record navigation or for linking a subform? By
unbound, I mean that the ControlSource property of the combo is empty.

It sounds as though you may be using a combo box on the EmployeeID field
of a payroll record and by changing the value you are actually changing
the employee associated with displayed payroll data. Hopefully I am
wrong

Provide a bit more detail about your form and I'll be glad to try to help
you figure it out.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Annelie wrote:
I retrieve individual payroll data via employeeselect combo box. If the
employee selected has no data in the current payroll, the previous
employee's data stays on the screen. Is there such a thing as, if no
data found in sub reports, show sub empty, or clear subreport.