Open form to certian record

[vols77]

Just to make sure I explain this I like to use a list - sorry if it seems "elementary" to some...
1) The form to enter / update data [form name = Jobs] is set up as a datasheet to best resemble Excel.
2) Switchboard has a button that says to Update / Add to existing job
3) I need a list box to appear so that once the user clicks the "update..." button they can select the record they want to update or add to.
4) If possible I would like the list to be auto expanding so that once a new job is added, next time they click on the button that job will automatically be displayed. This avoids the requirement for someone to go in and manually maintain a list.

I have been out of programming for some time now so any help is greatly appreciated!

Thanks!

 

[Remou]

Create a listbox using the wizard. It should walk you through the steps. Make sure you select the Job ID as the first column. You can then use the Click event of the list box to open a form based on the record selected. For example:

[tt]DoCmd.OpenForm "frmJobs",,,"JobID=" & Me.lstJobs[/tt]

A listbox based on a the Jobs table will automatically expand its list as each new job is added. You will need to requery if you keep the list form open while you add jobs.

 

[vols77]

How do I have the list only show the jobs once? I.E. I have the list box linked back to the main table where all the records reside. The problem is that since there can be multiple entries for one job, the list box lists the job as many times as there are entries.

If I have the list box linked to a query (where I know I can get the job "list" correct), will this option still work?

 

[Remou]

You should have a table that contains only jobs. You should use that for your list box. If you do not have such a table, you should read:

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

It is possible to base a listbox on a query or an SQL string, if you do this you can use SELECT DISTINCT, but it may not suit.

 

[vols77]

Okay,
A table has been created to have JobIDs.

Table: JobID
Field: Job

Now the list box is linked to this table and the following code used on the On Click of the list box:

DoCmd.OpenForm "Jobs", , , "Job=" & Me.Job

Form Name: Jobs
Field on Form: Job

The problem is that by using the above code the following error appears....

Method or data member not found and it highlights me.job

Any suggestions??

 

[Remou]

What is the name of the control that holds the list?

 

[vols77]

On the form "Switchboard", there is a field "List15" that is a list box linked back to Table "JobID" field "Job". What I need is that once the user selects the job from the list box (essentially the onclick of the listbox), the form "Jobs" will open with only the selected job from the list box.

the field on the form "jobs" is "Job"

Basically have the form "job" open with its field "job" = to the field "Job" from the table "JobID".

 

[PHV]

DoCmd.OpenForm "Jobs", , , "Job=" & Me!List15

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vols77]

Almost there.... not the only thing is that a pop up comes up that says "enter Parameter Value" and has the job I want listed. If I type in the job again, the form opens but that really defeats the purpose....

Any ideas?

 

[PHV]

DoCmd.OpenForm "Jobs", , , "Job='" & Me!List15 & "'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vols77]

Going great but it will not open in the datasheet view.... tried changing code but no luck.... Really need it to open in datasheet to prevent "complaints".

Thank you again to all for the help!

 

[PHV]

Put the cursor inside the OpenForm word in your code and then press the F1 key.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vols77]

Thank you! Took some time but figured out it was acformds for "datasheet".

 

[vols77]

A problem has arised..... one of the issues is that I would like the list to expand as jobs are added. That is why there is a make table query named "mkjoblist" that creates the table JobID.
What I would like to do is that once the user clicks the button "button1" on the switchboard, the following will happen..
1) make table query runs
2) list15 becomes visible to allow the user to select a job.

I have the list15.visible = true, ok, but it is the running the query.

First, I tried, to make a macro that:
1) warnings off
2) run query
3) warnings on

then referenced the macro via... docmd.runmacro "joblist"
but the following message appears...

"....could not lock the table 'JobId' because it is already in use...."


Initially, I would like to avoid the runmacro, but come up with the same if I write the code.....

Is this where I need to use "requery"? If so, how would I do that in this instance?