Link to home
Start Free TrialLog in
Avatar of JoeBarbone
JoeBarbone

asked on

select records based on user input Access 2010

Hello,

I have a database with information in it regarding jobs that employees are performing. They frequently need to update their records as items on a particular job# are completed. What I would like to do is prompt the user for their EmployeeID and ONLY display those records in a form/popup/etc and then have them select one of their own records from the list that was just displayed, then bring up the full form for that selected record for updating/editing.

This should be possible, correct? There are currently two tables, employees and jobs, employees has EmployeeID and EmployeeName and the jobs table has all of the job information plus EmployeeID.

Any information would be greatly appreciated.

Thank you in advance!

Joe
Avatar of trbaze
trbaze

if your form record source includes EmployeeID then set the form filter with an InputBox.
In the 'Else' part of the statement is two ways of doing this, depending on whether the form is already open or not.

Dim varEmployee as Variant

varEmployee=InputBox("Please enter employee ID:")
If IsNull(varEmployee) or varEmployee="" then     'nothing was entered

Else
     'If opening a form use docmd.OpenForm
     Docmd.OpenForm "JobsForm",,,"[EmployeeID]=" & varEmployee

     'If form is already open then use Form.Filter
     Form_JobsForm.FilterOn=False
     Form_jobsForm.Filter="[EmployeeID]=" & varEmployee
     Form_JobsForm.FilterOn=True
End If

Open in new window

Avatar of JoeBarbone

ASKER

ok, I'll try that.

But, how do I then select the particualr record from the filtered list?
Is the list that they choose from a combobox or listbox or what?  if so, does that need to be filtered instead of the form, or both?
at the moment, it's a subform, but I can change it if necessary, I just need to find out how. lol

This is going to be on a workstation next to the machine they are using for the job. The user walks up to the PC and selects a form they want to "fill out", it then prompts them for their EmployeeID. After they enter their EmployeeID, I want show them all of the records they currently have open, if there are none, open a new record.

How can I show them the records they already have open without using a subform?

Thank you for your replies!

Joe
the attached file is screen cap of the Enter Menu. They enter their four digit EmployeeID and their records populate the sub-form below.

I need to figure out a way to select one of the records from the subform to display on the real form for editing.Does that make sense?
EnterMenu.png
If your subform is linked to the mainform, say by JobID or EmployeeID, then access should automatically filter it along with main form.  If not, you can add a line in the 'Else' part of the statement after filtering the mainform to requery the subform.  Example:

Dim varEmployee as Variant

varEmployee=InputBox("Please enter employee ID:")
If IsNull(varEmployee) or varEmployee="" then     'nothing was entered

Else
     'If opening a form use docmd.OpenForm
     Docmd.OpenForm "JobsForm",,,"[EmployeeID]=" & varEmployee

     'If form is already open then use Form.Filter
     Form_JobsForm.FilterOn=False
     Form_jobsForm.Filter="[EmployeeID]=" & varEmployee
     Form_JobsForm.FilterOn=True
     Forms_JobsForm.subformname.requery     'requery subform
End If 

Open in new window

Ok, cool. I understand the code and what it's doing, but the screen shot I posted is a form that is not attached to the form that needs to be edited. Having said that, where does this code go?

And this still doesn't address how I select a record to edit from the sub-form. (or does it)

The screen cap attached previously works properly. I just need to figure out how to select a record to edit from the results on the sub-form.

Thanks again.

Joe
ASKER CERTIFIED SOLUTION
Avatar of trbaze
trbaze

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial