Query Value and Destination Fields are not the same?

[laperouse]

I am using Microsoft Access to update a table on a SQL Server. The table on the SQL Server has an autogenerated number in it so I cannot just update the table and be done with it. I have written some code (will follow) to generate a number in the table and also to update the table. The problem I am having is that for my insert statement I get the following error:

Run-time error '3346': Number of query values and destination fields not the same.

Here is my insert statement nested into a do while loop:

Do While Not rst.EOF

UserRec = "Insert INTO [UsersTemp] ([UserID], [Location], [FullName], [Dept]) Values ('" & NxtUserID & " , " & Location & ", " & E - Mail & ", " & Department & "')"

myDB.Execute UserRec

NxtUserID = NxtUserID + 1

Loop

The table it is going into (UserRec) has the following design:

UserID - number
Location - text
FullName - text
Dept - text

The values going into UserRec table are from different tables, but I create a query to pull them together as follows:

sqlStr = "SELECT [Lafayette Inventory].[Location], [Lafayette Inventory].[Department], [Lafayette Inventory].[E-Mail] FROM [Lafayette Inventory] LEFT JOIN Users ON [Lafayette Inventory].[E-Mail]=[Users].[FULLNAME] WHERE ((([Users].[FULLNAME]) Is Null));"

[Lafayette Inventory].[Location] - text
[Lafayette Inventory].[Department] - text
[Lafayette Inventory].[E-Mail] - text

The code to generate the UserID is also a query and is as follows:

sqlStr2 = "SELECT dbo_LASTUSER.LASTUSER FROM dbo_TIUSER INNER JOIN dbo_LASTUSER ON dbo_TIUSER.USERID = dbo_LASTUSER.LASTUSER;"

This information is pulled from a table on the SQL Server which has the following design:

LastUser - Number

I cannot figure out why I am getting that error. From what I see, the query values and the destination fields are the same. Any help will be greatly appreciated.

Thanks!

 

[VDavid]

laperouse

For what I see the insert statement

UserRec = "Insert INTO [UsersTemp] ([UserID], [Location], [FullName], [Dept]) Values ('" & NxtUserID & " , " & Location & ", " & E - Mail & ", " & Department & "')"

should be

UserRec = "Insert INTO [UsersTemp] ([UserID], [Location], [FullName], [Dept]) Values ('" & NxtUserID & "' , '" & Location & "', '" & EMail & "', '" & Department & "')"

You were missing the ' in the fields.
I'm guessing about your fields type, I put then all like text type, if you have a numeric field just erase the ' around it.

Hope this helps.
David.

 

[laperouse]

Thanks! That did make the error message go away, but now I get an Overflow error??

I get it on the line in the do-while loop where I am trying to increase the autogenerated number?

 

[JCruz063]

laperouse,
Why are you trying to generate an number that is "Auto-generated" ? That's somewhat contradictory, and I'm not sure I fully understand it.

In any case, where exactly are you getting the overflow error? Is it at this line NxtUserID = NxtUserID + 1? Overflow errors occur when you try to assign a value that is out of the range of allowed values for the variable you're assigning the value to (if that makes sense). For example, A variable of type Byte can hold values from 0 up to 255 (all positive). A variable of type Integer can hold values from -32768 to 32767, and so on. If you attempt to assign a value greater than 255 to a Byte variable, you'll get an Overflow error. The same is true if you try to assign a value smaller than -32768 or greater than 32767 to an Integer variable.

OK, after all that (perhaps unnecessary) lecture, here's the answer to your problem. In your loop, you're not advancing the cursor position of the recordset, and thus the loop never ends. Add a rst.MoveNext inside the loop and your problem will be solved. In any case, though, make sure that NxtUserID is of a type that's capable of holding all the possible values allowed.

[COLOR=darkblue]Do While Not[/color] rst.EOF
   [COLOR=green]' ...[/color]
   myDB.Execute UserRec    
   NxtUserID = NxtUserID + 1

   [green]' This was missing before[/green]
   rst.MoveNext;    
[COLOR=darkblue]Loop[/color]

JC




_________________________________________________
To get the best response to a question, read faq222-2244.

 

[laperouse]

Thanks! That did work. I didn't even think about that. And the lecture wasn't unnecessary...I needed it to understand fully what I was doing wrong...So thanks for the extra time you put in. Now I am just having a problem with the data actually popluating into the table, but I'll get it.

Thanks again!

LD

 

[JCruz063]

Now I am just having a problem with the data actually popluating into the table, but I'll get it.

What problem are you having?


JC

_________________________________________________
To get the best response to a question, read faq222-2244.

 

[laperouse]

The script runs, but nothing is populated into the UsersTemp table. I know that the query is correct in the script because I created it in Access as a regular query and it pulls what it is supposed to. When I open the UsersTemp table, nothing is there.