RE: Trouble using DLookup

[stuarta]

What I have is a database that logs all print jobs. I have a couple of fields which I would like to fill in automatically. These fields are called (just to get started):-<br>
<br>
Print Run<br>
Size of Paper<br>
<br>
I also have a field called &quot;Job&quot;.<br>
<br>
When the user enters the job name and it recognises it in the table, I would like it to automatically fill in the &quot;Print Run&quot; and &quot;Size of Paper&quot;.<br>
<br>
These values are obviously already stored in the database from the previous issue. It's probably very simple, but I have tried several ways and just can't get my head round.<br>
<br>
I would be very grateful if someone could help me out.<br>
<br>
Thanks<br>
<br>
<br>

 

[elizabeth]

Can you be more specific about what you're trying to do with the data? If you only wanted to display it in a form based on user input, you wouldn't need DLookup. The user would use Find (on the Edit Menu and on the toolbar) on the Job field.

 

[stuarta]

What I would like it to do is basically have the jobs which are regular weekly jobs automatically fill-in certain information, to save the user having to re-input all the data. If need be I could just have a button or something for the user to click when they want the information to be filled in.

 

[elizabeth]

Sorry, I'm not getting it. Every week you want the user to enter job numbers, but you already have the job numbers, plus related data (printrun, sizeofpaper) in your Access tables. What are you trying to accomplish in terms of the business problem: do you want the user to give you additional related data for specific jobs? Or do you want the user to specify jobs so that you can do something with them, like display them back to the user in a form, query, or report, or copy it to use somewhere else? And do you want to display/use one job at a time or have a user enter a list of job numbers?

 

[WP]

Stuart,<br>
<br>
Having entered the job name you want to find the last record of that job and retrieve the Print Run and Size of Paper, Yes ?<br>
<br>
So, in the After Update event of the Job Name field you want some code .<br>
<br>
You could use the FindLast method.<br>
<br>
But here’s a better way:<br>
<br>
Dim dbs as Database<br>
Dim rsPrint as RecordSet<br>
<br>
Set dbs=Currentdb<br>
Set rsPrint=dbs.Openrecordset(&quot;SELECT tblPrints WHERE Job=’&quot; & txtJob & &quot;’;&quot<br>
<br>
<br>
if rsPrint.RecordCount=0 then<br>
Msgbox &quot;No Previous prints &quot;<br>
else<br>
txtPrintRun= rsPrint![Print Run]<br>
txtPaperSize=rsPrint![Paper Size]<br>
endif<br>
<br>
rsPrint.close<br>
<br>
In my example I have used names which should be close to those you have.<br>
<br>
Hope this helps.<br>
<br>
WP<br>
<p>Bill Paton<br><a href=mailto:wpaton@neptune400.co.uk>wpaton@neptune400.co.uk</a><br><a href=

http://www.neptune400.co.uk>www.neptune400.co.uk</a><br>

 

[stuarta]

Thanks, I can use this for something else I am looking at, but basically what I want the main item to do is for creating weekly reports.<br>
<br>
What we do is every week, we create reports on what jobs we have. Most of the jobs are consistent, regular jobs. At the moment, the only way I have of these jobs appearing on the weekly report is to re-input them. The job name box I have is a combo box so that as soon as the user starts typing the name, it automatically starts to recognise it. Now it takes a lot of time to re-input the rest of the data which is the same from week to week. It seems that there must be an obvious way for it to automatically pick up this data from the table which is already stored there. ( I have actually got a second table which stores just the regular info, which is what the job name box is pointing to.)<br>
<br>
There is probably an easier way to this.<br>
<br>
Hope this helps and thanks anyway.

 

[WP]

You may well be able to use the inormation direct from your combo box. In the rowsource of the combo include the additional fields and set the column count accordingly. You can then reference these extra columns as combo.column(1), combo.column(2), etc where (1) is actually the second column. Thus on in the After Update event of the combo:<br>
<br>
txtPaperSize=combo.column(1)<br>
txtJobRun=combo.column(1)<br>
<br>
etc.<br>
<p>Bill Paton<br><a href=mailto:wpaton@neptune400.co.uk>wpaton@neptune400.co.uk</a><br><a href=

http://www.neptune400.co.uk>www.neptune400.co.uk</a><br>

 

[stuarta]

Yes I think this might do it, but I haven't looked at the columncount property that much.<br>
<br>
Will my Print Run box have to be a combo box or can I leave it as a straight text box like it is at the moment.<br>
<br>
Sorry to be a pain.

 

[WP]

Text box is fine for Print Run as I presume you will be filling this from combo.column(x), yes ?<br>
<br>
WP <p>Bill Paton<br><a href=mailto:wpaton@neptune400.co.uk>wpaton@neptune400.co.uk</a><br><a href=

http://www.neptune400.co.uk>www.neptune400.co.uk</a><br>

 

[stuarta]

Sorry, like I said I haven't used the column count property before so I am not really sure what I am doing with it, and where I have to put the expressions and such like.

 

[stuarta]

I am close. I have actually decided to use a button which the user clicks after typing the job name. The button runs an update query, which looks at the table abd updates the figures. I have got to the stage that it tells me that is going to update 5 rows, but doesn't. <br>
<br>
I just need to know the expression to tell it that the &quot;Print Run&quot; in the main job table equals the &quot;Print Run&quot; in the regular table.<br>
<br>
Thanks again.

 

[DougP]

Code to add info to a text box from a combo<br>
<br>
Private Sub Combo3_AfterUpdate()<br>
Me!Text1 = Combo3.Text<br>
End Sub<br>
<br>
have the report look at the Text box<br>
using the following code<br>
Forms![Form1]![Text1]<br>
<br>
or look at the combo box directly<br>
Forms![Form1]![Combo3]<br>
<br>
Use the BUILD wizard it will make the correct syntax for you.<br>
<br>
Hope this helps<br>

 

[stuarta]

I am actually thinking now, that it must be simple enough to create a button which runs a macro or whatever to look at the job name, which looks at another table, and if it matches, fill in the relevant information from that table.<br>
<br>
But I don't know how.

 

[elizabeth]

Yup. You could save steps by using the button to run an update or append query to add the data from your other table. But you may be able to save the step of duplicating the data too, if you want the data duplicated in the second table just to run your weekly reports. You can instead just capture the user input, then use that as the criteria to restrict the query that your report is based on.

 

[elizabeth]

Correction: The post I was referring to was originally posted on 10/26/99 but the posts are listed in order of last reply which was 11/1/99.

 

[stuarta]

Yeah. That is exactly what I have been working on. Trying to get an update query to work when I click the button. And unfortunately, I cannot find the original post. I don't really want the data to be duplicated into both tables, I just want the data to be added to the main job database. The trouble is, is that I am struggling, because as you have probably guessed, I am new to this.

 

[DougP]

Adding data to another table is an Append query not an Update query.<br>
Difference is Update changes items in fields Append adds new records to a table.<br>
<br>
You can change from one type the other very easily <br>
Click on the queries TAB click <br>
Open said query in design view<br>
Click &quot;Query&quot; Menu option (at the top)<br>
you will see Append, Update, etc. there<br>
Now when you pick Append it will ask you which Table in a dialog box to append to.<br>
The table in design view that you see wants to be the table you are appending from<br>
<br>
Hope this Helps somewhat<br>
DougP<br>