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
Microsoft Access

Avatar of undefined
Last Comment
trbaze

8/22/2022 - Mon
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

JoeBarbone

ASKER
ok, I'll try that.

But, how do I then select the particualr record from the filtered list?
trbaze

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?
Your help has saved me hundreds of hours of internet surfing.
fblack61
JoeBarbone

ASKER
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
JoeBarbone

ASKER
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
trbaze

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

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
JoeBarbone

ASKER
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
trbaze

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question