VB Question

[jjsaw5 via AccessMonster.com]

I am wondering if it is possible to change the WHERE clause of this statement
to select the current record of a form.

Lets say on a search form a user searchs for a record, i would like them to
be able to press a button and have that record put into another table. I have
generated this code ( which is pretty much and append query) but im wondering
if how i can put this into VB code and how i can change the WHERE clause to
select the current record on a from.



INSERT INTO SRMisc ( [Tracker Item], Description, Comments, Requestor, [SR
Num] )
SELECT Tracker.X5, Tracker.Z1, Tracker.Z2, Tracker.Z3, Tracker.Z6
FROM Tracker
WHERE (((Tracker.Z6)="3918"));

 

[Guest]

Assuming you have a control on your form that has the value you want to
filter on,
WHERE (((Tracker.Z6)= '" & Me.txtZ & "'));"

 

[John W. Vinson]

im wondering
if how i can put this into VB code and how i can change the WHERE clause to
select the current record on a from.

Don't even need the VB code! Just change it to

WHERE (((Tracker.Z6)=[Forms]![YourFormName]![SomeControlName]));

referencing the form and control with the desired value of Z6.

John W. Vinson [MVP]

 

[jjsaw5 via AccessMonster.com]

I was able to accomplish what i wanted with this code....


Private Sub Command24_Click()
Dim strSQL As String

strSQL = "INSERT INTO SRMisc ( [Tracker Item], Description, " & _
"Comments, Requestor, [SR Num] ) " & _
"SELECT Tracker.X5, Tracker.Z1, Tracker.Z2, Tracker.Z3, Tracker.Z6
" & _
"FROM Tracker WHERE Tracker.Z6='" & Me.Z6 & "' OR Tracker.X5='" &
Me.X5 & "';"
DoCmd.RunSQL strSQL
End Sub


It worked fine until i added the OR clause, then i got this error

Run-time error '3464'

Data type mismatch in criteria expression

 

[John W. Vinson]

I was able to accomplish what i wanted with this code....


Private Sub Command24_Click()
Dim strSQL As String

strSQL = "INSERT INTO SRMisc ( [Tracker Item], Description, " & _
"Comments, Requestor, [SR Num] ) " & _
"SELECT Tracker.X5, Tracker.Z1, Tracker.Z2, Tracker.Z3, Tracker.Z6
" & _
"FROM Tracker WHERE Tracker.Z6='" & Me.Z6 & "' OR Tracker.X5='" &
Me.X5 & "';"
DoCmd.RunSQL strSQL
End Sub


It worked fine until i added the OR clause, then i got this error

Run-time error '3464'

Data type mismatch in criteria expression

So is it working ("I was able to accomplish...") or not ("until I added the OR
clause")?

What's the datatype of X5? If it's Text you need the quotemark delimiters; if
it's number, they should be omitted.

John W. Vinson [MVP]

 

[jjsaw5 via AccessMonster.com]

Sorry let me clarify what i was trying to say before.

I was able to accomplish what i wanted to do to an extent. It works if the
record i am trying to move has data in the Z6 field, but some records do not
have data in Z6 but they have data in X5. These are both number fields. When
i try to and the OR clause to the code it generates the error i gave above.

hope that makes clears things up a little, and thank you for your help!

 

[John W. Vinson]

Sorry let me clarify what i was trying to say before.

I was able to accomplish what i wanted to do to an extent. It works if the
record i am trying to move has data in the Z6 field, but some records do not
have data in Z6 but they have data in X5. These are both number fields. When
i try to and the OR clause to the code it generates the error i gave above.

If they are Number datatype fields (as opposed to Text fields which happen to
contain only numeric characters), get rid of the quotemark delimiters:

strSQL = "INSERT INTO SRMisc ( [Tracker Item], Description, " & _
"Comments, Requestor, [SR Num] ) " & _
"SELECT Tracker.X5, Tracker.Z1, Tracker.Z2, Tracker.Z3, Tracker.Z6" & _
"FROM Tracker WHERE Tracker.Z6=" & Me.Z6 & " OR Tracker.X5=" &
Me.X5 & ";"
DoCmd.RunSQL strSQL
End Sub

Text fields require either " or ' as delimiters for the searched value;
Date/Time fields require #; Number fields should have no delimiter at all.

John W. Vinson [MVP]