dlookup code

[slimjen]

Hello All,
Using access 2003. I have a edit form that users use in a database with a field they want autopopulated. I am using the same concept of the zipcode table lookup on the same form that is working fine. But when I apply the same concept to another field; it's not working. ex Table contains states with regions; Ex txtstate txtregiondesc. I want a field call country to autopopulate based on the states in the table. If the state = sc on the form I want the country field to = northeast because in the table sc=northeast.
I hope I'm making sense. the following is the code I'm using base on the zipcode lookup concept.

Private Sub txtCONSIGNCTRY_Exit(Cancel As Integer)
Dim txtSTATE As String
txtSTATE = DLookup("txtState", "qryREGIONS", "txtREGIONDESC =[txtCONSIGNCTRY] ")
'If (Not IsNull(txtSTATE)) Then Me![txtCONSIGNSTATE] = txtSTATE
End Sub

where am i going wrong please.

[boblarson]

You need to move the criteria out of the quotes and concatenate it in and add quotes:

txtSTATE = DLookup("txtState", "qryREGIONS", "txtREGIONDESC =" & Chr(34) & Me.txtCONSIGNCTRY & Chr(34))

[June7]

Anything within quotes is a literal string. Variables must be concatented.

DLookup("field to search", "source table/query", "criteria expression")

If the criteria expression uses a literal value, then: "fieldname=myvalue"

If the criteria expression must get value from a variable: "fieldname=" & myvariable

If the field is text, use apostrophe delimiters, if date use #, number uses none:
"fieldname='" & myvariable & "'"
"fieldname=#" & myvariable & "#"

But have to ask why are you saving region, country to another table? This is duplication of data. Only save the state then the other info can also be retrieved by joining tables. Basic principle of relational database - don't copy data, relate records.

[slimjen]

Ok. I modified the code as you suggested and the field is still not populating. I also tried putting the code the after update thinking that after the "txtCONSIGSTATE" field is filled in when you tabbed to the next field, it would populate the field.

Private Sub txtCONSIGNCTRY_Exit(Cancel As Integer)
Dim txtSTATE As String
txtSTATE = DLookup("txtSTATE", "tblREGIONS", "txtREGIONDESC =" & Chr(34) & Me.txtCONSIGNCTRY & Chr(34))
End Sub

Maybe it's not looking at the the correct field. I need it to refer to the state field on the form "txtconsigstate" = SC lookup the "txtSTATE" field from the tblREGIONS and populate the txtCONSIGNCTRY" field with "txtREGIONDESC" from the tblREGIONS associated with SC.
Why does the concept work with the zipcode and not this. Help pls.
Thank you

[June7]

Again, why duplicating data?

The expression you show doesn't fit the description of what you want. Chr(34) is not correct character and not needed anyway.

txtCONSIGNCTRY = DLookup("txtREGIONDESC", "tblREGIONS", "txtState='" & Me.txtState & "'")

Why is this in txtCONSIGNCTRY Exit event?

[boblarson]

Again, why duplicating data?

The expression you show doesn't fit the description of what you want. Chr(34) is not correct character and not needed anyway.

June7 - CHR(34) is a DOUBLE QUOTE and is perfectly well and fine to use there. It is NOT incorrect and you misstated that.

You can use a double quote OR a single quote (single quote is also CHR(39) by the way. And you can use the CHR in those places instead of using "'" or """.

[slimjen]

Again, why duplicating data?

The expression you show doesn't fit the description of what you want. Chr(34) is not correct character and not needed anyway.

txtCONSIGNCTRY = DLookup("txtREGIONDESC", "tblREGIONS", "txtState='" & Me.txtState & "'")

Why is this in txtCONSIGNCTRY Exit event?

Where should I put this please.
Thanks

[June7]

Oh, okay, thanks Bob, will keep that in mind. Just so used to actually typing the apostrophe.

I don't know what event you should put this in. Maybe form Close or some button Click or AfterUpdate of txtState. I really do not understand why you want to save this data into another table.

[slimjen]

Maybe i'm not explaining it correctly. When the user opens the form; most of the time the city and state information is already there. The "txtconsignctry" field is a combo box they have to select. The users are asking if that field can be populated with the corresponding region based on the state field on the form. I have a table called regions for the dlookup with two fields: txtstate and txtregiondesc ie. record SC and US Northeast Region. I have tried both afterupdate and exit and neither works with the code i've been using. I was mentioning the same concept works with the zipcode field i am using but not the consignctry field. I need to know what I am doing wrong.
Thanks

[June7]

You earlier stated: "I need it to refer to the state field on the form "txtconsigstate" = SC lookup the "txtSTATE" field from the tblREGIONS and populate the txtCONSIGNCTRY" field with "txtREGIONDESC" from the tblREGIONS associated with SC."

Now you say user selects value for txtCONSIGNCTRY. If user selects the value for txtconsignctry, then you don't need code to populate that value.

If tables are properly related, should not need to duplicate data. If you just want to show related info on forms or in reports, then join to the tables in the RecordSource.

Need to know more about your data structure. Is more than one table involved in the state, region, country data? Do you want to provide the project?

[boblarson]

Oh, okay, thanks Bob, will keep that in mind. Just so used to actually typing the apostrophe.

I use the Chr(34) almost exclusively so I don't have to be mindful of fields (particularly last names and business names) which might have a single quote in them (i.e. O'Brien, Bob's Towing, etc.) which then would cause an error if you use single quotes.

[boblarson]

Just include the region in the combo's row source and refer to it from the control (just to look at, not to store because you don't need to store redundant data as it is already defined and stored elsewhere).

The control source of the text box would be something like:

=[ComboNameHere].[Column](1)

Where 1 is the second column, so if it was the fourth column you would use a 3 as it is zero-based.

[slimjen]

Ok; I'm not following. If I use this code

=[ComboNameHere].[Column](1)

what is this using as a source. Do I us the table "tblregions" as the row source? If yes; how will it know to look at the "txtState" field on the form to determine the "txtRegiondesc"? Im so lost.

[June7]

Bob suggested you have a multi-column combobox that has state, region, country as columns (visible or not depending on column width). Then textboxes can refer to the combobox columns as sources for related info to just display. Review tutorials at http://datapigtechnologies.com/AccessMain.htm especially the 3 on comboboxes in the Access Forms: Control Basics section.

That is one method to show info from related records. Another is by joining tables.

We are both advising that you not duplicate data to multiple tables.

[boblarson]

Ok; I'm not following. If I use this code

=[ComboNameHere].[Column](1)

what is this using as a source. Do I us the table "tblregions" as the row source? If yes; how will it know to look at the "txtState" field on the form to determine the "txtRegiondesc"? Im so lost.

Okay, let's start over.

1. You have a combo box which you are making a selection from, correct?

2. This combo box has a row source. The row source should be a select statement which includes the data for which you are selecting for that particular field it is bound to on the form but it can have more in the row source than just a single table.

See this example to get a better idea of what I am talking about:
http://downloads.btabdevelopment.com...llInFields.zip