Value put in textbox for field in same table as combo box selection

[david.paton]

I tried to write an explanation as to what I want but I couldn't seem to get it right

I have a form frmCrisisSupportWorkers with a tab control on the form. On the first tab, Personal Info there is a combo box called cboLocation. It is next to the Town/City.
I have a text box that updates the postcode after the selection is made below it but that is as the post codes are stored in the combo box and I just put the column for the postcode in the control source of the text box.
I can't do that for state as I have it stored in another table and use a numeric identifier to show which state is selected.

I want to have a text box below that auto updates the state once a town is selected but I can't seem to get it working.

I think I have explained it well enough.

I have uploaded my database for people to see what I am talking about.

 

[GinaWhipp]

I think this is what you want...

 

[david.paton]

Do you mean the text box is updating with the ID of the state as I wanted the name of the state to appear here?

 

[GinaWhipp]

If you pick the Location the State name will update. You mean you want it to appear in the TEXT box also?

 

[david.paton]

I only have a number appearing in that text box where the state should be

 

[GinaWhipp]

Okay, I made it so the State will appear once you select the state. Give me a second and I'll also make it appear in the TEXT box.

 

[GinaWhipp]

Okay, see if this is what you want...

 

[david.paton]

That is perfect, thanks, how did you do it?

 

[GinaWhipp]

Look in the control, I used a DLookup().

 

[david.paton]

I am now having trouble in my form, frmSearch, getting the search function for “Total hours by suburb” and “Total hours by TC Location” working.



At the moment, I have only been trying to make the query qryIssHoursBySurburb to see if I can get that working then I would copy the format to the other queries and use dlookup to get the value put in the text box next to the corresponding heading.

Can someone help me get this query working as I don't know how to get it summing hours stored in the activity table for locations chosen from a combo box under the above headings on frmSearch please?

The combo box I am working on is called cboSuburb with the query name qryIssHoursBySurburb. In my worker table, called tblCrisisSupportWorkers, the location is stored as a numeric value derived from another table. That field name is cLocationID.

I think that is all the information I need to write.

 

[GinaWhipp]

Hmm, you need to make some adjustments...

1. You can't use a query that has parameters in a DLookup(). You need to use a query without parameters.
2. The Query name in the DLookup() must match the name of an actual query in your database. I see in a couple of cases yours don't, didn't check them all.

 

[david.paton]

How can I do this,

I added the location field to the query from tblLocations and entered this in the criteria [forms]![frmSearch]![cboSuburb] but it still didn't find the hours I have entered for a worker from that suburb.

 

[david.paton]

Here is the database with the added criteria under the location field in the criteria of the query. The query runs but it returns nothing, so at least there is no error but I should have a number.

 

[GinaWhipp]

You cannot use parameters with a Dlookup(), it does not work. Do you know how to use a DLookup()? Perhaps this will help...
http://www.techonthenet.com/access/functions/domain/dlookup.php

 

[david.paton]

Not sure what you mean, but I think I just got it working. I just needed to have a field in the query from tblCrisisSupportWorkers to establish the link between tblLocations and tblActivities.

 

[david.paton]

I looked at that link but I still don't understand what you mean by "cannot use parameters with a dlookup function"

 

[david.paton]

Your comment Gina forced me to go back and examine the control source for the text boxes to try and work out what you were saying and I realised that somehow the control source became =DLookUp("SumofaNumberHours","qry_qrySvHoursBySuburb").

I have no idea how the extra qry_ got in there but I removed it and now it seems to be working so thanks.

 

[GinaWhipp]

As long as it's now working...

 

[david.paton]

This is my final version. Could people have a look please and see if it looks alright? The home form with links to everything else is frmhome.

Thanks,
Dave

 

[GinaWhipp]

Looking now...