473,322 Members | 1,241 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

New to Access and VBA, Please Help

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
2 1111
nico5038
3,080 Expert 2GB
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
Great. Thanx!!!
Dec 4 '06 #3

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

Similar topics

11
by: Wolfgang Kaml | last post by:
Hello All, I have been working on this for almost a week now and I haven't anything up my sleeves anymore that I could test in addition or change.... Since I am not sure, if this is a Windows...
27
by: Chuck Grimsby | last post by:
(Repost, due to lack of submissions...) The Microsoft Access Product Group (the people who build Microsoft Access) want your help! One of the main things we're working on for the near future...
2
by: BT Openworld | last post by:
I have just had to upgrade to Access 2003 as Access 97 EMail (SendObject) doesn't work when loaded on Windows XP. I'm finding my way around Access 2003 but my biggest problem is getting...
4
by: Otis Hunter | last post by:
I have been given an Access Database which contains a table that has an OLE object field that contains a Word document. That table contains hundreds of records. I would like to find out how I can...
5
by: Chand | last post by:
Private Sub tbDate_AfterUpdate() Dim cnn As New ADODB.Connection Dim rst As New ADODB.Recordset rst.CursorLocation = adUseClient Set cnn = CurrentProject.Connection rst.Open "SELECT * FROM...
13
by: Joner | last post by:
Hello, I'm having trouble with a little programme of mine where I connect to an access database. It seems to connect fine, and disconnect fine, but then after it won't reconnect, I get the error...
15
by: Cheryl Langdon | last post by:
Hello everyone, This is my first attempt at getting help in this manner. Please forgive me if this is an inappropriate request. I suddenly find myself in urgent need of instruction on how to...
7
by: Cheryl Langdon | last post by:
Does anyone know if there is a way to globally turn off ALL control tips in Access 2003 using VBA code? Thanks. --- CL
9
by: Wayne Smith | last post by:
I've come up against a major headache that I can't seem to find a solution for but I'm sure there must be a workaround and I would really be grateful of any help. I'm currently building a web...
0
by: uno7031 | last post by:
Help Please!!! Adding 5 Days to another Date in an access query Good Morning, Help please…. I am new to access and trying to write a query that will add 5 days between a RecDate and a...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.