• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 208
  • Last Modified:

Query Question

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
Jennerator
Asked:
Jennerator
  • 12
  • 10
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
JenneratorAuthor Commented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
JenneratorAuthor Commented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
JenneratorAuthor Commented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
JenneratorAuthor Commented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<< 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
 
JenneratorAuthor Commented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
JenneratorAuthor Commented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
oops... in your sSQL change the word 'VALUES' to 'SELECT'.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
JenneratorAuthor Commented:
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
 
JenneratorAuthor Commented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
yep.

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

0
 
JenneratorAuthor Commented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Tomorrow morning.  How's it going partner?
0
 
JenneratorAuthor Commented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Anytime.
-Jim
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 12
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now