select records based on user input Access 2010

JoeBarbone
JoeBarbone used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
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

Author

Commented:
ok, I'll try that.

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

Commented:
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?
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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

Author

Commented:
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

Commented:
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

Author

Commented:
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
Commented:
Thanks for the screen shot.  Here's what I would do to make this work (assuming your textbox is called 'txtCode'):

1. Create an 'Enter' button next to your unbound textbox(textbox name is 'txtCode').

2. Set the 'Default' property of the button to 'Yes'. (It's located in the 'Other' tab of the properties window.)  This will make hitting the enter key like clicking th button.

3. Change your subform to an unbound listbox.

4. Set the following properties of the listbox: (In this example i'm using five columns- JobID, EmployeeID, EmployeeName, OrderNumber, and Operator.)  If you have more fields in the listbox, adjust your columns and column widths accordingly.
    In Data Tab-
      BoundColumn: 1
      LimitToList: Yes
   
   In Format Tab-
     ColumnCount: 5
     ColumnWidths: 0";0";1";1";1"

5. In the OnClick event of the 'Enter' button add this code:
'This code will set the rowsource of the listbox, filter by employeeid, and requery the listbox

'You can set the rowsource to what you want
ListBox1.RowSource="SELECT JobTable.JobID, EmployeeTable.EmployeeID,
EmployeeTable.EmployeeName, JobTable.OrderNumber, JobTable.Operator
FROM EmployeeTable INNERJOIN JobTable ON EmployeeTable.EmployeeID = JobTable.EmployeeID
WHERE (((EmployeeTable.EmployeeID)=" & me.txtCode & "));"

ListBox1.Requery

Open in new window

6.  In the listbox1 OnDblClick event:
Docmd.OpenForm "JobsForm",,,"[JobID]=" & me.ListBox1.Column(0)

Open in new window

The listbox1 column(0) should represent the JobID field.
Let me know if this helps.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial