By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,027 Members | 1,261 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,027 IT Pros & Developers. It's quick & easy.

New to Access and VBA, Please Help

P: 2
Hello,

I am trying to have a user select an option (country name) from a combo box, then the VB code looks up the country name in the Country_List table and returns the address field associated with it (I use DLookup and it works fine). Then I try moving this field into a new table, but when I do so the value of the address variable goes blank, and I get a window that prompts me to enter the field value. I am trying to do this in order to generate a temp table that I can use later to generate a report. Please let me know If I am going about this the wrong way. Here is my code. Please note that is works for all the other variables but the one containing DLookup (STAD). If I debug, the value is there till it steps into the RunSQL.



STAD = DLookup("[Address]", "Country_List", "[Country] = '" & Country & "'")
SQL = "INSERT INTO Order_Data(Country, LCE, OEN, HosN, Address) VALUES (Country, LCE, OEN, HosN, STAD)"
DoCmd.RunSQL SQL
Dec 4 '06 #1
Share this Question
Share on Google+
2 Replies


nico5038
Expert 2.5K+
P: 3,072
It's against normalization to store a STAD like this as we normally extract the data from the table when needed by a join.

For making your code work try:

STAD = DLookup("[Address]", "Country_List", "[Country] = '" & Country & "'")
SQL = "INSERT INTO Order_Data(Country, LCE, OEN, HosN, Address) VALUES (Country, LCE, OEN, HosN, '" & STAD & "')"
DoCmd.RunSQL SQL

Probably you need the same stringing for the other fields. Make sure that text is surrounded by single quotes and dates by a #. Numbers don't need that.

Nic;o)
Dec 4 '06 #2

P: 2
Great. Thanx!!!
Dec 4 '06 #3

Post your reply

Sign in to post your reply or Sign up for a free account.