Solved

Query Question

Posted on 2004-09-29
22
182 Views
Last Modified: 2012-06-21
Hello,

I have a query that will append to a table. In the query I have some calculated fields that pull data from an unbound field on a form. For example I have a list of items to be ordered. Then I want the current customers ID to appear next to each item ordered and the date. But in the grid when I put...

PSDbID:  [Forms]![Material Details]![cboname]

and the next column

DateOrdered:  [Forms]![Material Details]![dateordered]

Here is the current SQL...
SELECT Materials.CategoryID, Materials.Title, Materials.Order, [Forms]![Material Details]![cboName] AS PSdbID, [Forms]![Material Details]![OrderDate] AS Orderdate
FROM Materials
WHERE (((Materials.Order)=True));

The problem is instead of getting the ID number that does exisit on the Material Details form I get a square. Then for the orderdate, I get nothing.

Can someone out there please tell me what I am missing? I hope this makes sense.

Thanks,
Jenn~
0
Comment
Question by:Jennerator
  • 12
  • 10
22 Comments
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
Just a few thoughts...
(1)  Check the spelling of your form and controls
(2)  Make sure the 1st column of the combo box is the ID you want to grab
(3)  I see a SELECT query, and your question states you want to APPEND records to another table?
(4)  Make sure your form is open when the query runs

-Jim
0
 

Author Comment

by:Jennerator
Comment Utility
Thanks for your response Jim. I have also thought of those points.

1.) I used the build feature to make sure I spelled everything right
2.) I check the combo box and the ID is in the bound column
3.) I just want the select query to work first then I will change it to an append
4.) I am positive that the forms involved are open.

I have never tried to use an existing field as a calculation before. I use them all the time as criteria. Could that cause any problem that you are aware of?

Any other ideas?
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
Let's try this.  Create a command button on your form, and in it's OnClick event write the following

msgbox "My cboName is " & [Forms]![Material Details]![cboName]
msgbox "My order date is " & [Forms]![Material Details]![OrderDate]

Save, then go into Form view and hit the button.

This will at least determine if it's referencing the fields correctly.
0
 

Author Comment

by:Jennerator
Comment Utility
Good Idea, I tried it and the msgbox's come up with the correct data so now we know that they are being referenced correctly. Now how do we get that in the query?

Jenn~
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
I'm not finding it, so instead how about executing it within code..  Replace the msgbox's with this code, then check the table to see if it appended correctly...

'Make sure you have a reference to ADO.

dim cn as adodb.connection
set cn = currentproject.connection

dim sSQL as string

sSQL = "INSERT INTO Materials (CategoryID field, title field, Name field, orderdate field) "
sSQL = sSQL = "VALUES (" & Me.CategoryID & " as foo1, '" & me.Title & "' as foo2, '" & Me.cboName & "' as foo3, #" & me.OrderDate & "# as foo4)

cn.execute sSQL

I'm assuming that categoryID is a number.  
0
 

Author Comment

by:Jennerator
Comment Utility
I tried your code above, I get the following error on the last line... cn.execute sSQL

Runtime Error -2147217900 (8004oe14)

Invalid SQL Statement: Expected 'Delete', 'Insert', 'procedure', 'Select', or 'Update'

I have made sure that Microsoft ADO Ext. 2.7 for DDL and Security is checked. I didn't see any other ADO reference to add.

Any suggestions?

0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
Microsoft ADO Ext. 2.7 for DDL and Security is checked = ADOX, not ADO.
Microsoft ActiveX Data Objects 2.1 Library = ADO.  

Sorry, should have been more specific.

Dump your entire SQL string, as typed into code, here.  We probably have a comma or something equally ridiculous missing.

-Jim
0
 

Author Comment

by:Jennerator
Comment Utility
Thanks for bearing with me. I have to have this working today, so I am a little desprate. I do have the ActiveX Data Object 2.1 library checked already.

Here is the code, I just copied it from your post and pasted it. I modified the Table name that the data will be dumped into, but that is all a changed.

Here it is...

Dim cn As adodb.Connection
Set cn = CurrentProject.Connection

Dim sSQL As String

sSQL = "INSERT INTO tbl_Materialsactivity (CategoryID field, title field, Name field, orderdate field) "
sSQL = sSQL = "VALUES (" & Me.CategoryID & " as foo1, '" & Me.Title & "' as foo2, '" & Me.cboName & "' as foo3, #" & Me.OrderDate & "# as foo4)"

cn.Execute sSQL
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
<< CategoryID field, title field, Name field, orderdate field >>

Replace these with the actual field name in your tbl_Materialsactivity table.  If they have any spaces in them, surround them with square brackets [ ]

The rest looks fine.
0
 

Author Comment

by:Jennerator
Comment Utility
I am still getting the same error. I have modified it again here is what I am running...

sSQL = "INSERT INTO tbl_Materialsactivity (Materials, CommunicationID, Order ,DateOrdered) "
sSQL = sSQL = "VALUES ('" & Me.Title & "' as foo1, " & Me.cboName & " as foo2, " & Me.Order & " as foo3, #" & Me.OrderDate & "# as foo4)"

1.) Materials should = Title and is text
2.) CommunicationID should = cboName and it is a number Field
3.) Order = Order is Yes/no field
4.) DateOrdered = orderdate is Date field

Could you please check the "" now. I think I may have messed them up.

Thanks,
Jenn~
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
Appears to be correct....  After your sSQL creation, type the keyword STOP.  Then run this, and you should stop in debug mode where the STOP line is.  hit Ctrl + G to go into the Immediate window.  Type ?sSQL and hit Return in the Immediate window, and dump whatever it displays here.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:Jennerator
Comment Utility
By the way, I don't see anywhere in the code that states WHERE the Order = True. Does that need to be added? And where would it go in the code?

As you can tell I am a newbie with SQL and VBA So thanks for your patience.

Jenn~
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
oops... in your sSQL change the word 'VALUES' to 'SELECT'.
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
oops again, use below

sSQL = "INSERT INTO tbl_Materialsactivity (Materials, CommunicationID, Order ,DateOrdered) "
sSQL = sSQL & "SELECT '" & Me.Title & "' as foo1, " & Me.cboName & " as foo2, " & Me.Order & " as foo3, #" & Me.OrderDate & "# as foo4 "


0
 

Author Comment

by:Jennerator
Comment Utility
All I get in the immediate window is "False"

I put the Stop after the

sSQL = "INSERT INTO tbl_Materialsactivity (Materials, CommunicationID, Order ,DateOrdered) "
sSQL = sSQL = "SELECT ('" & Me.Title & "' as foo1, " & Me.cboName & " as foo2, " & Me.Order & " as foo3, #" & Me.OrderDate & "# as foo4)"

Stop

cn.execute ssql

Is that the right spot?
0
 

Author Comment

by:Jennerator
Comment Utility
O.K. now we are getting somewhere. Here is what it dumpped out...

INSERT INTO tbl_Materialsactivity (Materials, CommunicationID, Order ,DateOrdered) SELECT 'Tips for newly diagnosed' as foo1, 16 as foo2, 0 as foo3, #9/29/2004# as foo4
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
yep.

sSQL = sSQL =    --->   sSQL = sSQL &
Also, remove the ( ) 's after the word SELECT.

0
 

Author Comment

by:Jennerator
Comment Utility
I don't quite understand your last post. there are no ( ) after the word SELECT.

Here is what it looks like now....
sSQL = "INSERT INTO tbl_Materialsactivity (Materials, CommunicationID, Order ,DateOrdered) "
sSQL = sSQL & "SELECT '" & Me.Title & "' as foo1, " & Me.cboName & " as foo2, " & Me.Order & " as foo3, #" & Me.OrderDate & "# as foo4 "

What does sSQL = sSQL =    --->   sSQL = sSQL &
mean?

Once we get this I will up the point to as much as I got!!!

Thanks for sticking with me.
Jenn~
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
Looks good.  When you are in Debug mode (i.e. in code with the yellow box) you can execute code one line at a time by hitting your {F8} button.  Do this through your cn.Execute line, then go to your table, and see if it added the record to your table.

{f5} means run through all remaining lines.

I have to run home now.  Good luck with this, I'll check in tomorrow.
-Jim
0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 100 total points
Comment Utility
Tomorrow morning.  How's it going partner?
0
 

Author Comment

by:Jennerator
Comment Utility
Well, I had to get the problem fixed last night due to a demo meeting on the database first thing this morning. After you left, I still had some errors in the code and since my experince is so limited in that area, I went back to plan A.

I originally had the display fields in a main form and the materials in a sub form. I would have bet money that that would not have made a difference in the query, but when I combined them into one form, I was able to see the cboName in the query. So I went with that.

I want to thank you for spending so much time with me and not making me feel like I didn't know anything. I also appriciate the learning experience you gave me with code.

Have a great Day,
Jenn~
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
Anytime.
-Jim
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now