append values from another table

[buratti]

I have a customers form (based on the customers table). I need for a value from another table (a table named addresses) be placed in a field on my customers form based on the value of the address field in the customers form. For example... I start to enter new customer data, then after I enter the address, lets sat 123 Main St., I need the DB to lookup "Main St". from the addresses table and tell me what days it is serviced and enter that value in the Service Days field of the customers table/form. BTW, the Addresses table is basically just a list for service days of all streets serviced.

I figured to just do this in the after update event of the adderss field. I placed a dlookup function to retrieve the value but I cant get the syntax correct. I have the following:

Dim Days As String
Dim CurrentStreet As String
 
'[COLOR=green]remove the house number leaving just the street name[/COLOR]
CurrentStreet = Mid(Nz([Address]), InStr(Nz([Address]), " ") + 1)
[COLOR=green]'lookup the value in the Addresses table that matches the current street[/COLOR]
Days = Nz(DLookup("days", "addresses", "streetname =  " & CurrentStreet), "")
Me.Service_Days = Days

After that code is ran I get the following error:

Runtime Error 3075: Syntax error (missing operator) in query expression 'streetname = main st'
"main st" being the address I entered for the customer

What am I doing wrong??

Now, to expand on this... Once I get that above code working, I need to figure out how to update all previous customers to update the service days field automatically. This code or function is something that will only need to be performed once (or if the service day for any street ever changes which is very rare). I was thinking just a button somewhere that did the following, but needs to be translated into code:

"open customers table/form and go to first record"
"lookup current address in Addresses table to get service days value"
"enter value in service days field"
"go to next record and repeat till the last record is updated"

I would figure an update query, but I dont know how to set different criteria for each record to be updated. How would I go about doing this also? Thanks for any help!!!

 

[pbaldy]

This should help with your syntax problem:

http://www.mvps.org/access/general/gen0018.htm

I suspect an update query would be the way to go. If the tables can be joined on the street field, that would be the most efficient way I think.

 

[buratti]

Thanks for the response... I've seen and referenced that site before, but I don’t think it's helping me in my situation. I am trying to use a variable in the criteria section and that site mentions nothing about variable syntax. The Help file in access gives a good example of how to use a variable, and I copied the syntax EXACTLY as the help file but it's still throwing up errors. The code by itself is:

DLookup("days", "addresses", "streetname = " & CurrentStreet)
I know it has to be something like a simple ' or two I am missing somewhere, but WHERE?

As for the second part of my question... The Update Query... The tables can be joined in the street field, but sorry, I have no clue on how to write/structure this particular query. I can do simple updates (meaning update all fields to the same value) but lost on entering a different value with different criteria for each record. Anyone that can expand on this also would be appreciated. Thank You

 

[pbaldy]

Your value is a string; you're using the syntax appropriate for a numeric value. The appropriate example would be this one:

DLookup("FieldName", "TableName", "Criteria = '" & forms!FormName!ControlName & "'")

substituting your field name for the form reference.

 

[buratti]

Thanks that worked!!! I knew it had to be something simple. What about the update query now to change all existing customers?

 

[pbaldy]

Can the tables be joined on street? Can you post the db, or a sample of it?

 

[buratti]

I believe that they can. I posted this same question a little while ago and someone answered/suggested that I normalize my data first. That original post is here: http://www.access-programmers.co.uk/forums/showthread.php?t=186553
I then split my Addresses table into 3 separate tables, according to at least what I understand the normalization rules as explained in the post link above. Once I join them (by join I am assuming in the relationship window) how would I "update" each customer record to display the correct service days? I simplified my DB and attached. Thanks for taking the time to look at it and help me. I really appreciate it!!!!

 

[pbaldy]

Is the AddressID field in the customers table supposed to be filled with an AddressID from the addresses table? Currently they are all blank. If not, there appears to be no common field. In the addresses table, you only have the street, while in the customers table, the field includes the house number.

 

[buratti]

Is the AddressID field in the customers table supposed to be filled with an AddressID from the addresses table?

Oops, sorry. That field was suppose to be deleted. It was for something I tried before that wasn't working.

If not, there appears to be no common field. In the addresses table, you only have the street, while in the customers table, the field includes the house number.

Well that's part of my problem. I need to remove the house number from the address field then "lookup" that street in the addresses table. A quick thought I just had was adding a field to the customers table named street only. In that field we'll extract only the street name from the address field leaving the common field you were asking for above. I added that field, updated its contents and deleted that Address ID field then reattached the DB. Would this be something to work with now?

 

[pbaldy]

Does this do what you want?

UPDATE Customers LEFT JOIN Addresses ON Customers.[Street Only] = Addresses.StreetName SET Customers.[Day ID] = [addresses].[dayid];

Depending on your needs, you may want an INNER JOIN instead of the LEFT JOIN.

 

[buratti]

Thanks for your help!!! That worked great! Just one more quick question and I'll stop bothering you Everything is working now and I probably shouldn't change anything if it is wrong, but is the structure of all my tables correct? Also, in the Customer details table, is the control source for the DaysID field the correct practice, or should I have included the Addresses and/or Days table in the underlying query and set the conrtol source to one of those tables instead. It's not that important of a question though. Like I said, it's working now and I probably shouldn't touch it, but just trying to learn proper design/structure for future reference. Thanks so much for your other help!