query, now what

[Karen]

I'm trying to write a sample program that will give us the pricing as setup
in our business system.

What I have:
A SQL table (Prccode) in the business system database that I have linked to.
The table structure is as follows:
code_type, filler, startdate, enddate, pord,qty1,price,qty2,price,...
'filler' can contain one of the following: customer number/code, customer
number/product category, customer type/code, customer type/product category,
customer number only, code number only, customer type only, product category
only. The code_type tells us which of these to expect in filler. These
have to be searched in this order because if the customer number/code is
found we use that price code and stop looking and if it's not found we look
for customer number/product category, and so on.

What I need to do: I need to have the user enter the customer number, code,
product category, quantity ordered, and customer type, then lookup in
Prccode the record where filler is a combination of these. I was thinking I
would run a query looking for the customer number/code combination then if
nothing is found try customer number/product category and so on. I know how
to use docmd.openquery and run a query (I have a query working now that
shows me the record based on the customer number/code). What I don't know
is how to use the results of the query for anything except showing it on the
screen or in a form. I need to have the program look at the results of the
query and get the startdate and enddate to compare to the system date to see
if the price code is valid, then get the price based on the quantity ordered
that was entered. And if there are no results to the first query I need to
run the second and so on.

Can anyone give me a shove in the right direction on how to use the results
of the query?

Thank you for all of the help.

Karen

 

[MtnView]

Hello,

If I understood you, then the code below put inside the click event of
a button called CheckFillerCmd on your form could get you started. I
assumed that you would have the fields named CustomerNumber, Code,
ProductCategory, and CustomerType on the form. It seems possible that
you might have to check the code_type because different combinations of
data might yield the same filler, but I didn't approach it that way.
Basically the code checks for a match on 3 of the situations. If a
match is found, it checks the dates. The statements like "MsgBox
("CustomerNumber/Code = ")" are not necessary, but show which condition
is matched.

On Error GoTo Err_CheckFillerCmd_Click

Dim rst As Recordset
Dim MyWksp As Workspace
Dim MyDB As Database
Dim MySQL As String, MyCriteria As String

Set MyWksp = DBEngine.Workspaces(0)
Set MyDB = MyWksp.Databases(0)

MySQL = "SELECT * FROM Prccode WHERE filler = '"
MyCriteria = Me!CustomerNumber & "/" & Me!Code & "'"

Set rst = MyDB.OpenRecordset(MySQL & MyCriteria, dbOpenForwardOnly)
If Not rst.EOF Then
MsgBox ("CustomerNumber/Code = ")
Else
MyCriteria = Me!CustomerNumber & "/" & Me!ProductCategory & "'"
Set rst = MyDB.OpenRecordset(MySQL & MyCriteria,
dbOpenForwardOnly)
If Not rst.EOF Then
MsgBox ("CustomerNumber/ProductCategory = ")
Else
MyCriteria = Me!CustomerType & "/" & Me!Code & "'"
Set rst = MyDB.OpenRecordset(MySQL & MyCriteria,
dbOpenForwardOnly)
If Not rst.EOF Then
MsgBox ("CustomerType/Code = ")
Else

End If
End If
End If

If Not rst.EOF Then 'found a match above
If Date > rst!StartDate And Date <= rst!EndDate Then
MsgBox (rst!StartDate)
Else
MsgBox ("bad date")
End If
Else
MsgBox ("NO MATCH")
End If
rst.Close


Exit_CheckFillerCmd_Click:
Exit Sub

Err_CheckFillerCmd_Click:
MsgBox Err.Description
Resume Exit_CheckFillerCmd_Click

(Note if a line wraps back to the first column it should be put back on
a single line in the code.)

 

[Karen]

Thank you for your reply. The code looks like it should do what I'm looking
for. Except...

I'm having issues with an error about the line Dim MyWksp as Workspace. I
have looked in the help file and it says that i need to check the Data
Access Object in the References dialog box. I don't see 'Data Access
Object' in that list. Can someone point me in the right direction. The only
things checked in that list are:
Visual Basic for Applications
Microsoft Access 10.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.1 Library

I am using MS Access 2002 SP3.

Thanks.

Karen

 

[John Vinson]

I'm having issues with an error about the line Dim MyWksp as Workspace. I
have looked in the help file and it says that i need to check the Data
Access Object in the References dialog box. I don't see 'Data Access
Object' in that list. Can someone point me in the right direction. The only
things checked in that list are:
Visual Basic for Applications
Microsoft Access 10.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.1 Library

Scroll down alphbetically to

Microsoft DAO 3.6 Object Library

(or the highest version you find) and check it.

John W. Vinson[MVP]

 

[Karen]

Thank You!

That resolved the error.

Karen

Scroll down alphbetically to

Microsoft DAO 3.6 Object Library

(or the highest version you find) and check it.

John W. Vinson[MVP]