Help with some syntax

[dzirkelb]

I have a subform inside of a form. For that subform, on its focus, I wish to run some code. I want the code to run a query:

SELECT ORDERS.SPL_HNDL AS SplHandling, ORDERS.[ORDER #] AS OrderNum, ORDERS.[CUSTOMER #] AS CustNum
FROM ORDERS
WHERE (((ORDERS.[ORDER #])=[Forms]![ORDERS INPUT FORM]![ORDER #]));


I then want to assign the results of SplHandling to a variable strSplHandling and the results of CustNum to a variable strCustNum and OrderNum to variable intOrderNum

Now, I want to do an if then statement like this:

if len(strComments) < 2 then
SELECT [SPL HNDL] FROM [CUSTOMER MASTER] WHERE ([CUST #] = 'strCustNum') and assign it to a variable called strSplHandlingCustMaster

UPDATE ORDERS SET SPL_HNDL = 'strSplhandlingCustMaster' WHERE (ORDER = intOrderNum)
end if

I know what needs to be done, and I can code it in other languages; however, I am not very good at VB...can anyone help please?

 

[KeithG]

You need to open up the Select statement in a Recordset to retrieve the values from the query.


dim myRec as DAO.Recordset

set myRec=currentdb.openrecordset("[Your SQL]")

 

[dzirkelb]

here is what I currently have

Dim strSplHandling As String
Dim strCustNum As String
Dim intOrderNum As Integer
Dim strSplHandlingCustMaster As String
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("[SELECT ORDERS.SPL_HNDL AS SplHandling, ORDERS.[ORDER #] AS OrderNum, ORDERS.[CUSTOMER #] AS CustNum FROM ORDERS WHERE (((ORDERS.[ORDER #])=[Forms]![ORDERS INPUT FORM]![ORDER #]))]")
strSplHandling = rs("SplHandling")
strCustNum = rs("strCustNum")
intOrderNum = rs("intOrderNum")

If Len(strSplHandling) < 2 Then
    Set rs = CurrentDb.OpenRecordset("[SELECT [SPL HNDL] AS SplHandling FROM [CUSTOMER MASTER] WHERE ([CUST #] = 'strCustNum&')]")
    strSplHandlingCustMaster = rs("SplHandling")
    
    UPDATE ORDERS SET SPL_HNDL = 'strSplhandlingCustMaster' WHERE (ORDER = intOrderNum)
End If

questions:

am I assigning the varialbes correctly? strCustNum = rs("CustNum")

on my one sql statement,

Set rs = CurrentDb.OpenRecordset("[SELECT [SPL HNDL] AS SplHandling FROM [CUSTOMER MASTER] WHERE ([CUST #] = 'strCustNum&')]")

I am sure i am wrong where I do ([CUST #] = 'strCustNum&')]")...how do I assign a variable in there?

Last question...how do I execute an update query instead for the update at the end?

 

[KeithG]

You SQL for the recordset should look like the below

"[SELECT [SPL HNDL] AS SplHandling FROM [CUSTOMER MASTER] WHERE [CUST #] = '" & strCustNum & "' And SPLHandling='=" & strSPLHandling & "'"

Is customer Number really a string or a Number.


Next, to run the update query you need to use Docmd.runSQl("YourSQLStatement")
strSplHandlingCustMaster = rs("SplHandling")

 

[KeithG]

also to retrieve the values from the recordset use the below syntax

rs.Fields("YourFieldName")

 

[dzirkelb]

Set rs = CurrentDb.OpenRecordset("[SELECT ORDERS.SPL_HNDL AS SplHandling, ORDERS.[ORDER #] AS OrderNum, ORDERS.[CUSTOMER #] AS CustNum FROM ORDERS WHERE (((ORDERS.[ORDER #])=[Forms]![ORDERS INPUT FORM]![ORDER #]))]")

I get an error on that line saying can't find the input query or table...is it because of the
[Forms]![ORDERS INPUT FORM]![ORDER #]))]")

 

[KeithG]

It sounds like the query can't find the Orders table or query. is it there and spelled correctly?

 

[dzirkelb]

yes...it is spelled correctly. I created the query in access and simply copied / pasted the sql code into the vba code.

 

[dzirkelb]

Does it matter that the Orders table is a sql linked table?

 

[PeterF]

Set rs = CurrentDb.OpenRecordset("[SELECT ORDERS.SPL_HNDL AS SplHandling, ORDERS.[ORDER #] AS OrderNum, ORDERS.[CUSTOMER #] AS CustNum FROM ORDERS WHERE (((ORDERS.[ORDER #])=[Forms]![ORDERS INPUT FORM]![ORDER #]))]")

I get an error on that line saying can't find the input query or table...is it because of the
[Forms]![ORDERS INPUT FORM]![ORDER #]))]")

It can have something to do with the "[ ]" arround the SQL string and in VBA you can't use the query syntax of refering to a field on a form.
I think it should be something like this (I added linefeeds because I think it's easier to read) :

Set rs = CurrentDb.OpenRecordset("SELECT ORDERS.SPL_HNDL AS SplHandling, ORDERS.[ORDER #] AS OrderNum, ORDERS.[CUSTOMER #] AS CustNum " & _
"FROM ORDERS " & _
"WHERE (((ORDERS.[ORDER #])= [COLOR="red"]" & [/COLOR][Forms]![ORDERS INPUT FORM]![ORDER #] [COLOR="Red"]& "[/COLOR]))[COLOR="Red"];[/COLOR]")

I assumed that Order # is a number, if its text it should be:

"WHERE (((ORDERS.[ORDER #])= [COLOR="red"]'" & [/COLOR][Forms]![ORDERS INPUT FORM]![ORDER #] [COLOR="Red"]& "'[/COLOR]))[COLOR="Red"];[/COLOR]")

 

[dzirkelb]

I thought that also, and it didn't work. So, i replaced the form part with just an order number to see if it works, and it doesn't...here is what I havce that doen't work also:

Set rs = CurrentDb.OpenRecordset("[SELECT ORDERS.SPL_HNDL AS SplHandling, ORDERS.[ORDER #] AS OrderNum, ORDERS.[CUSTOMER #] AS CustNum FROM ORDERS WHERE (((ORDERS.[ORDER #]) = 418626))]")

 

[WayneRyan]

dz,

Looks great except for these square brackets:

Set rs1 = CurrentDb.OpenRecordset("[B][[/B]SELECT ORDERS.SPL_HNDL AS SplHandling, 
                                    ORDERS.[ORDER #] AS OrderNum, 
                                    ORDERS.[CUSTOMER #] AS CustNum 
                                    FROM ORDERS WHERE (((ORDERS.[ORDER #[B]][/B]) = 418626))]")

Wayne

 

[dzirkelb]

I removed square brackets so it looks like this:

Set rs = CurrentDb.OpenRecordset("SELECT ORDERS.SPL_HNDL AS SplHandling, ORDERS.[ORDER #] AS OrderNum, ORDERS.[CUSTOMER #] AS CustNum FROM ORDERS WHERE (((ORDERS.[ORDER #]) = 418626))")

and I get the follwing error:

run time error 3622. You must use the dbSeeChanges option when accessing a SQL Server table that has an IDENTITY column.

so I changed my query to:

Set rs = CurrentDb.OpenRecordset("SELECT ORDERS.SPL_HNDL AS SplHandling, ORDERS.[ORDER #] AS OrderNum, ORDERS.[CUSTOMER #] AS CustNum FROM ORDERS WHERE (((ORDERS.[ORDER #]) = " & [Forms]![ORDERS INPUT FORM]![ORDER #] & " ))", dbOpenDynaset, dbSeeChanges)

and it works correctly. Now I have the next lines of code:

If Not IsNull(rs.Fields("SplHandling")) Then
    strSplHandling = rs.Fields("SplHandling")
End If
strCustNum = rs.Fields("CustNum")
intOrderNum = rs.Fields("OrderNum")

I get a runtime error 6 overflow on the line intOrderNum = rs.fields("OrderNum")...any ideas?

 

[dzirkelb]

Ok, i have everything working now...yay! here isthe code that works:

Dim strSplHandling As String
Dim strCustNum As String
Dim intOrderNum As Double
Dim strSplHandlingCustMaster As String
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("SELECT ORDERS.SPL_HNDL AS SplHandling, ORDERS.[ORDER #] AS OrderNum, ORDERS.[CUSTOMER #] AS CustNum FROM ORDERS WHERE (((ORDERS.[ORDER #]) = " & [Forms]![ORDERS INPUT FORM]![ORDER #] & " ))", dbOpenDynaset, dbSeeChanges)
If Not IsNull(rs.Fields("SplHandling")) Then
    strSplHandling = rs.Fields("SplHandling")
End If
strCustNum = rs.Fields("CustNum")
intOrderNum = rs.Fields("OrderNum")

rs.Close

If Len(strSplHandling) < 2 Then
    Set rs = CurrentDb.OpenRecordset("SELECT [SPL HNDL] AS SplHandling FROM [CUSTOMER MASTER] WHERE ([CUST #] = '" & strCustNum & "' )")
    strSplHandlingCustMaster = rs.Fields("SplHandling")
    
    rs.Close
        
    DoCmd.RunSQL ("UPDATE ORDERS SET SPL_HNDL = '" & strSplHandlingCustMaster & "' WHERE ([ORDER #] = " & intOrderNum & ")")
End If

Now, I wish to take out the warning of "you are about to update one row...click yes or no."

Anyone know how to do that?

 

[dzirkelb]

nm, found it...DoCmd.SetWarnings false...thanks for all of your help!