SQL Query to set in variable

ardy

Registered User.
Local time
Yesterday, 23:59
Joined
Sep 24, 2012
Messages
98
Hi,

I am hoping somebody can shed some light into my issue here, I am not a programmer but I do OK. I have a form in which am gathering information from the user to populate a table(Customer Master List) and at the same time (btn_Copy_Click Event)transferring the info to another form(Case) populating another table(Customer_Call). It is working fairly well, My issue is that before transferring the info I need to run a check(SQL Query) to make sure the customer or actually the Well ID don't exist in the Customer_Call table. I am trying to do this in pieces .....

1) capturing the well_Id in a variable(WellID) in the first form and using that to build the sql string and query the Customer_Call table.
2)once that works place it in a if / else clause to copy or not with appropriate messages

With that I am stuck in step 1:banghead:
It works up until Set rst = CurrentDb.OpenRecordset(strSQL) the i get Run-time error '3061' Too few parameters. Expected 1.

Code:
Dim WellID As String
Dim strModel As String
Dim strSQL As String
Dim rst As DAO.Recordset

    WellID = Forms!f_Customer_Lookup.Well_ID
        MsgBox WellID ' testing to see if it picks up the correct box in form
        
    strSQL = "SELECT Customer_Call.[Cus_Well_ID] " & _
             "FROM Customer_Call " & _
             "WHERE Customer_Call.[Cus_Well_ID] = WellID;"
    Set rst = CurrentDb.OpenRecordset(strSQL)
    strModel = rst!Cus_Well_ID
    rst.Close
    MsgBox rst ' Testing to see if the strSQL captured the data
    Set rst = Nothing

End Sub

Any help is appreciated......
 
The Query you are trying yo run has a Parameter which needs to be concatenated. Try..
Code:
    strSQL = "SELECT Customer_Call.[Cus_Well_ID] " & _
             "FROM Customer_Call " & _
             "WHERE Customer_Call.[Cus_Well_ID] =[COLOR=Blue][B] " & WellID[/B][/COLOR]
I hope WellID is a Number.

BTW, you cannot simply display the Recordset in a message box, you will hit on Data Type Mismatch error. You need to either loop through the Recordset or use,
Code:
MsgBox RecordsetObjectName.Fields("FieldName")
[COLOR=Green]'Or[/COLOR]
MsgBox RecordsetObjectName!FieldName
[COLOR=Green]'Or[/COLOR]
MsgBox RecordsetObjectName.Fields(0) [COLOR=Green]'0 represents the first column, 1 the second so on....[/COLOR]
 
Thanks Paul for getting back to me.......I really Appreciate this

WellID is not a number it's alphanumerical (i.e 10S04E07N024), I changed the code to
Code:
Dim WellID As String
Dim strModel As String
Dim strSQL As String
Dim rst As DAO.Recordset

    WellID = Forms!f_Customer_Lookup.Well_ID
        MsgBox WellID
        
    strSQL = "SELECT Customer_Call.[Cus_Well_ID] " & _
             "FROM Customer_Call " & _
             "WHERE Customer_Call.[Cus_Well_ID] = " & WellID
    Set rst = CurrentDb.OpenRecordset(strSQL)
    strModel = rst!Cus_Well_ID
    rst.Close
    MsgBox RecordsetObjectName.Fields("Cus_Well_ID")
    Set rst = Nothing

I get the error Run-Time '3075' - Syntax error (missing operator) in query expression 'Customer_Call.[Cus_Well_ID] = 10S04E07N024'

Could it be that I am missing the ; at the end of sql string - WHERE Customer_Call.[Cus_Well_ID] = " & WellID should be WHERE Customer_Call.[Cus_Well_ID] = ;" & WellID

I guess the result I am after is that in first MsgBox I need to see 10S04E07N024 and I do, I am hoping that I see The same ID 10S04E07N024 in the second MsgBox. if this happens than I know it works because the query returned the ID which exist in the Customer_Call Table.
 
No the problem is because, if it is Alphanumeric you need to enclose them betweenn single quotes..
Code:
    strSQL = "SELECT Customer_Call.[Cus_Well_ID] " & _
             "FROM Customer_Call " & _
             "WHERE Customer_Call.[Cus_Well_ID] = [COLOR=Red][B]'[/B][/COLOR]" & WellID & "[COLOR=Red][B]'[/B][/COLOR]"
 
OK that worked, but I still get an error......
Run-time error '424' - Object required

The error is for MsgBox RecordsetObjectName.Fields("Cus_Well_ID")

Truthfully I guess the disconnect fro me is this, In concept I am executing an query(strSQL), conceptually this query has a result, in my case 10S04E07N024, or at least I hope..... I guess what I don't understand is that we can't set this result to a variable but we can use it by means of RecordsetObject, now can you explain what this RecordsetisObject is looking at is it the table (Customer_Call) or is it the form, what fields......?

The modified code per last comment
Code:
Dim WellID As String
Dim strModel As String
Dim strSQL As String
Dim rst As DAO.Recordset

    WellID = Forms!f_Customer_Lookup.Well_ID
        MsgBox WellID
        
    strSQL = "SELECT Customer_Call.[Cus_Well_ID] " & _
             "FROM Customer_Call " & _
             "WHERE Customer_Call.[Cus_Well_ID] = '" & WellID & "'"
    Set rst = CurrentDb.OpenRecordset(strSQL)
    strModel = rst!Cus_Well_ID
    rst.Close
    MsgBox RecordsetObjectName.Fields("Cus_Well_ID")
    Set rst = Nothing
 
What exactly were you hoping to see based on this line?

Code:
 MsgBox RecordsetObjectName.Fields("Cus_Well_ID")
 
The result of the query... the query returns a well ID right, in this case is 10S04E07N024......

Earlier in the code I am picking a WellID from the form
Code:
WellID = Forms!f_Customer_Lookup.Well_ID
with the query I am looking to see if the WellID exist in the Customer_Call table if it dose I will not transfer the information, it it dosn't then I transfer the information.........
So I guess what I am really wanting to see is what the query actually picked up.
 
RecordsetObjectName was a generic name I gave there to show that it should be replaced with a name of the Record Set Object you have in your code. In your case rst.
Code:
Dim WellID As String
Dim strModel As String
Dim strSQL As String
Dim rst As DAO.Recordset

    WellID = Forms!f_Customer_Lookup.Well_ID
        MsgBox WellID
        
    strSQL = "SELECT Customer_Call.[Cus_Well_ID] " & _
             "FROM Customer_Call " & _
             "WHERE Customer_Call.[Cus_Well_ID] = '" & WellID & "'"
    Set rst = CurrentDb.OpenRecordset(strSQL)
    strModel = rst!Cus_Well_ID
    [COLOR=Red][B]MsgBox rst.Fields("Cus_Well_ID")
    rst.Close[/B][/COLOR]
    Set rst = Nothing
 
It works for the first query and gives me the error for the second query.
Run-time error '3021': No Current record.

The Form . It is split form looking at table(Customer_Call). Top is the form bottom is the grid.

As in any split form once I highlight any record on the bottom I get the detail on the top. So the code works for the first pick, I get two message box the first one is telling me what is in the well_ID box on the top and sets it into a variable the second one tells me the result of the SQL. But the second pick gives me the error See above in red.
Somehow I think it is retaining the the first query in the memory I think, on the other hand isn't the Set rst = Nothing clears the result but maybe the strModel = rst!Cus_Well_ID is still holding to the value.......?:confused:

The latest Code
Code:
Dim WellID As String
Dim strModel As String
Dim strSQL As String
Dim rst As DAO.Recordset

    WellID = Forms!f_Customer_Lookup.Well_ID
        MsgBox WellID
        
    strSQL = "SELECT Customer_Call.[Cus_Well_ID] " & _
             "FROM Customer_Call " & _
             "WHERE Customer_Call.[Cus_Well_ID] = '" & WellID & "'"
    Set rst = CurrentDb.OpenRecordset(strSQL)
    strModel = rst!Cus_Well_ID
    MsgBox rst.Fields("Cus_Well_ID") & " -SQL String"
    rst.Close
    Set rst = Nothing
 

Users who are viewing this thread

Back
Top Bottom