New to Access VBA - Having trouble with SQL in VBA (1 Viewer)

michaelgiesbrecht

Registered User.
Local time
Today, 12:44
Joined
Jun 29, 2012
Messages
12
Hi everyone;

I'm fairly new to access vba programming; and I'm having some trouble getting my sql queries to work in VBA.

What I have is a form where the user can enter (or scan) a job number from a sheet. When they exit that field I have a vba procedure that takes the job number that they enetered and performs some string functions on it to make it usable for my query (the tables that I am interfacing with are part of our ERP system; so I can't change them......)

What I want to do is take that job number; split it into the two fields that I need (the job number uses this syntax : ordernumber"-"linenumber)

My code takes the job number and splits it into two variables; ordernum and linenum.

Now what I need to do is build a query that will search the ERB table and pull out the record that pertains to that order number and line number (it will always be a single record as the combination of order number and line number produces a unique record)

so what I want is this:

select (some stuff) from (erp tables) where table.ordernumber = ordernum and table.linnumber = linenum

I just can't seem to get the syntax to work, I always get an object variable not set error.

I then want to turn that into a record set so that I can pull information from that record into my form and eventually into my report.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 12:44
Joined
Aug 30, 2003
Messages
36,118
Hard to say why you get the error without seeing the code. ;)
 

michaelgiesbrecht

Registered User.
Local time
Today, 12:44
Joined
Jun 29, 2012
Messages
12
Wow. Um. I don't even know what to say to that. Need more coffee today obviously...

Here's the code that I'm working with:

Code:
Private Sub Job_Number_AfterUpdate()

Dim stringsql As String
Dim recordst As Recordset
Dim dbase As Database
Dim ordernum As String
Dim linenum As String
Dim jnum As String

'pull the job number from the form
jnum = JobNumber

ordernum = Left(jnum, InStr(1, jnum, "-") - 1)
linenum = Str(Val(Right(jnum, Len(jnum) - InStrRev(jnum, "-"))))
stringsql = "SELECT OPARTS.ORDNUM, OPARTS.LINENUMBER, OPARTS.PARTID, OPARTS.PARTREV, OPARTS.[DESC$], ORDER.PONUM, ORDER.NAME FROM OPARTS LEFT JOIN [ORDER] ON OPARTS.ORDNUM = ORDER.NUM WHERE ((oparts.ordnum) = " & ordernum & " );"
Set recordst = dbase.OpenRecordset(stringsql)
Customer = recordst.Fields("name").Value
PurchaseOrder = recordst.Fields("ponum").Value
PartNumber = recordst.Fields("partid").Value
Description = recordst.Fields("[desc$]").Value
Revision = recordst.Fields("partrev").Value

End Sub
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 12:44
Joined
Aug 30, 2003
Messages
36,118
Wasn't meant to be rude, if that's how you took it. I simply meant it would be difficult to say why you "always get an object variable not set error" without seeing the code. Having seen it, it's because you never set the dbase variable.
 

michaelgiesbrecht

Registered User.
Local time
Today, 12:44
Joined
Jun 29, 2012
Messages
12
I didn't take it as rude at all, sorry of I came off that way- I was meaning towards myself for forgetting to post code lol.

Sorry I'm quite new to access and vba; setting the dbase variable; should that be

Code:
set dbase = currentdb
?

Thanks so much for your help
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 12:44
Joined
Aug 30, 2003
Messages
36,118
Yes, that's how it would look. I would also disambiguate these lines:

Dim recordst As Recordset
Dim dbase As Database

to

Dim recordst As DAO.Recordset
Dim dbase As DAO.Database

Your code might work without, but you can run into problems since there's also an ADO recordset.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 12:44
Joined
Aug 30, 2003
Messages
36,118
No problem! Post back if you get stuck.
 

michaelgiesbrecht

Registered User.
Local time
Today, 12:44
Joined
Jun 29, 2012
Messages
12
It still does not want to execute this sql statement, am I separating the criteria correctly?

Code:
stringsql = "SELECT OPARTS.ORDNUM, OPARTS.LINENUMBER, OPARTS.PARTID, OPARTS.PARTREV, OPARTS.[DESC$], ORDER.PONUM, ORDER.NAME FROM OPARTS LEFT JOIN [ORDER] ON OPARTS.ORDNUM = ORDER.NUM WHERE ((oparts.ordnum) = " & ordernum & " ), ((oparts.linumber) = " & linenum & ");"
Set recordst = dbase.OpenRecordset(stringsql)
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 12:44
Joined
Aug 30, 2003
Messages
36,118
The two criteria would be separated by And or Or as appropriate to your needs, not a comma. Do both fields have numeric data types?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 12:44
Joined
Aug 30, 2003
Messages
36,118
Then try

"...WHERE oparts.ordnum = '" & ordernum & "' And oparts.linumber = '" & linenum & "'"
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 12:44
Joined
Aug 30, 2003
Messages
36,118
Could be something is spelled wrong, like "oparts.linumber", which based on the preceding SQL should be "linenumber"
 

michaelgiesbrecht

Registered User.
Local time
Today, 12:44
Joined
Jun 29, 2012
Messages
12
That was it. But now I have this:

Run-Time Error '3464':
Data Type Mismatch in criteria expression.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 12:44
Joined
Aug 30, 2003
Messages
36,118
That would imply that one or both of those fields in the criteria is numeric, not text. Another possibility is that the fields in the JOIN are of incompatible data types.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 12:44
Joined
Aug 30, 2003
Messages
36,118
Then back to the previous method without the single quotes, and correct the spelling.
 

michaelgiesbrecht

Registered User.
Local time
Today, 12:44
Joined
Jun 29, 2012
Messages
12
Ok so here is what I have now:

Code:
Option Compare Database

Private Sub Job_Number_AfterUpdate()

Dim stringsql As String
Dim recordst As dao.Recordset
Dim dbase As dao.Database
Dim ordernum As Variant
Dim linenum As Integer
Dim jnum As String

'pull the job number from the form
Set dbase = CurrentDb

jnum = JobNumber

ordernum = (Left(jnum, InStr(1, jnum, "-") - 1))
ordernum = Val(ordernum)
linenum = (Val(Right(jnum, Len(jnum) - InStrRev(jnum, "-"))))
stringsql = "SELECT OPARTS.ORDNUM, OPARTS.LINENUMBER, OPARTS.PARTIDg, OPARTS.PARTREV, OPARTS.[DESC$], ORDER.PONUM, ORDER.NAME FROM OPARTS LEFT JOIN [ORDER] ON OPARTS.ORDNUM = ORDER.NUM WHERE ((oparts.ordnum) = " & ordernum & " ) and ((oparts.linenumber) = " & linenum & ")"
Set recordst = dbase.OpenRecordset(stringsql)
Customer = recordst.Fields("name").Value
PurchaseOrder = recordst.Fields("ponum").Value
PartNumber = recordst.Fields("partid").Value
Description = recordst.Fields("[desc$]").Value
Revision = recordst.Fields("partrev").Value

End Sub

I'm still getting a "too few parameters" error on it, and I can't see why
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 12:44
Joined
Aug 30, 2003
Messages
36,118
Should I mention to check spelling again?

PARTIDg
 

Users who are viewing this thread

Top Bottom