Need help with this code

[Afrosheen via AccessMonster.com]

I have two tables I'm working with. One is called tblMain where the field I'm
working with is called supervisor. Table two is called tblSupervisor where
there is a field also called Supervisor.

Since we have supervisor changes I wanted to create an Old Supervisor/New
Supervisor conversion form.

What I'd like it to do is to be able to select the Old Supervisor from the
tblMain [though a drop down box. Combo4] create a query where it will just
show all the records that Supervisor has and then select the New Supervisor
from the tblSupervisor and replace the Old Supervisor. I did create a query
called qryOldNew where the control is from the tblMain. When I wrote the sql
part in the criteria it worked.

This is what I have so far.

Dim strSQL As String

strSQL = "SELECT supervisor" & _
" FROM [qryOldSup]" & _
" WHERE [supervisor]="" & Combo4&"""

Me.FilterOn = True

That's as far as I've been able to get. When I look at the query it doesn't
appear to be working.

Your help would be greatly appreciated. Thanks for reading my post.

 

[Paolo]

Hi Afrosheen,
you forgot to double the double quotes in your SQL. The correct sintax is as
follow

strSQL = "SELECT supervisor" & _
" FROM [qryOldSup]" & _
" WHERE [supervisor]=""" & Combo4 & """"

This is correct if supervisor is text and combo4 is also a text.

HTH Paolo

 

[Afrosheen via AccessMonster.com]

thanks for your help. I'll be able to finish up because now I have to replace
the old with the new. This is the code I believe will work. I set this up
with a command button.

100 Set dtm = CurrentProject.AccessConnection
110 sql6 = "update tblmain set supervisor =""" & Combo2 & """where
supervisor =""" & Combo4 & """"

120 dtm.Execute sql6, adCmdText + adExecuteNoRecords
130 Set dtm = Nothing

Hi Afrosheen,
you forgot to double the double quotes in your SQL. The correct sintax is as
follow

strSQL = "SELECT supervisor" & _
" FROM [qryOldSup]" & _
" WHERE [supervisor]=""" & Combo4 & """"

This is correct if supervisor is text and combo4 is also a text.

HTH Paolo

I have two tables I'm working with. One is called tblMain where the field I'm
working with is called supervisor. Table two is called tblSupervisor where

[quoted text clipped - 24 lines]

Your help would be greatly appreciated. Thanks for reading my post.