Full Version: Insert Into
UtterAccess Forums > Microsoft® Access > Access Forms
Hi Everyone
thought I had this cracked but yet again I get an error when I run this Code
Private Sub cmdMembers_Click()
Dim intBegin As Integer
Dim intEnd As Integer
Dim intMembershipID As Long
Dim intDateTransferred As Date
Dim strPrefix As String
Dim intRingNrFromID As Integer
Dim intRingNrID As Integer
Dim intTransferredToID As Integer
Dim strMemberName As String
intMembershipID = Me.MembershipID
intDateTransferred = Me.DateTransferred
intTransferredToID = Me.TransferredToID
strMemberName = Me.MemberName
strPrefix = Me.Prefix
intBegin = Me.RingNrFromID
intEnd = Me.RingNrID
Do While intBegin <= intEnd
CurrentDb.Execute "INSERT INTO tblMembersRingList ( MembershipID, DateTransferred, MemberName, TransferredToID, Prefix, RingNr ) " & _
" VALUES (" & intMembershipID & ", " & TransferredToID & ", " & strMemberName & ", " & Format(Me.DateTransferred, "\#dd\-mmm\-yyyy\#") & ", '" & strPrefix & "'," & intBegin & ")"
intBegin = intBegin + 1
CurrentDb.Execute "UPDATE tblMembersRingList SET Transferred = True WHERE RingNr Between " _
& Me.RingNrFromID & " And " & Me.RingNrID & ";", dbFailOnError
MsgBox "Those ring numbers have been inserted successfully"
End Sub
Error 3346 Nr of query values and destination fields are not the same
Your help appreciated
Hi Mike,
You might try storing the SQL statement in a string variable so you can examine what it actually looks like when being executed.
Just my 2 cents... 2cents.gif
R. Hicks
Notice that the data for the specific fields you want to insert does not appear to be in the correct order .....
The first field is MembershipID .. and you are populating it with ... the value from intMembershipID ... that appears OK.
The second field in the append query is ... DateTransferred ... but you are trying to populate that field with the value from ... TransferredToID ...
Also .. you are attempting to put ... Format(Me.DateTransferred, "\#dd\-mmm\-yyyy\#") as the value for the field named ... TransferredToID in the append query ...
think you need to check the field order in the append query and check that the values you want to append are correct.
You have six insert fields, but only 5 values going in separated by commas. I think that's the problem. The error message in this case is exact
It may look like you have six fields going in, but I think maybe you don't because of the extra/unclear quotes situation here:
"\#dd\-mmm\-yyyy\#") & ", '" & strPrefix
US date format is required when using Access SQL. You can fix this by changing Format(Me.DateTransferred, "\#dd\-mmm\-yyyy\#") to
Format(Me.DateTransferred, "\#mm\/dd\/yyyy\#")
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.