Pre-Populate a form

[sandeep0000]

im using access 2003
i want to thank everyone , i gotten alot of help from you guys/ladys. im not that strong at access,im good at crystal reports

i am creating a form
a person will enter there Worker number.
i want to have all the case numbers for that worker number to populate on each form record.
so if a worker has 5 cases, there will be 5 form records with a case number on each page.

is this a simple or possible in access?
Thank you

 

[sandeep0000]

i do have a table set up that has all the workers case id's and worker id and other info.

so i just need to make the form and table link so when the enter the woker id , it will bring up there cases on each form record

 

[discover142]

hi, how are ya.
I think the easiest way to do it is to use a query.
or you can also use the DLookUp function.
it'd make more sense if you list your fields..

 

[discover142]

maybe i understood wrong. So you need to see all 5 record on one page? perhaps what you are looking for is continuous form

 

[sandeep0000]

no, i it wouldnt be on one page

one record per page

so if this worker has 5 cases , there will be 5 pages with the case id on each one.

how would i set up a dlookup

one of my fields is cs_id that would poupulate when the enter worker_id

 

[discover142]

well dLookUp will look something like

me.txtcsid = DLookUp("cs_id","table","worker_id='" & me.[txtWorkerID] & "'")
this will not be able to populate all records on one form.

I'd recommend using a text box and a list box that populates after event of the text box.

 

[sandeep0000]

would i do this in the code builder?

 

[discover142]

try this.
Create a form and add a textbox (txtWorkID)
and a list box (lstCsID).

Double click the list box to open up it's property.
on row Source:, type in
SELECT tbl.worker_id, tbl.cs_id FROM tbl WHERE((TBL.worker_id)=forms!formname!txtworkid));

'tbl should be change to name of your table.
and don't forget to change the column count to 2.

Then set up afterevent of text box to requery the list box and it should work.

 

[sandeep0000]

SELECT dbo_Wpr_table.EW, dbo_Wpr_table.cs_id FROM dbo_Wpr_table WHERE ((dbo_Wpr_table.EW)=(forms!dbo_wpr1!txtworkid));


is what i entered in the row source of the list box


when i open the form it doesnt to anything to the list box or text box

 

[sandeep0000]

acutally it aske me

Forms!dbo_wpr1!txtworkid

i put in the workerid and when the form opens it puts the
worker number in the list box

 

[Remou]

It may be easies to use the wizards. You may also wish to consider subforms. I would recommend that you have two tables:

tblWorkers
WorkerID -> Primary key
Name
Etc

tblCases
CaseID -> Primary key
WorkerID -> Foreign key
Etc
(

http://www.r937.com/relational.html)

Create a relationship between these two tables in the relationship window by dragging the WorkerID from tblWorkers to tblCases. You are now in a position to create a form and a sub form, the Access 2003 wizards will guide you through this (the Categories form in the Northwind database is an example). Next, you can add a combobox to the main form and choose "Find a record on my form" from the options presented by the wizard. This will allow you to select a worker id and/or name and to go to that record. This is quite a conventional set-up and you should find a number of examples.