How to use SQL query in VBA function?

[tujurikkuja]

I am using Access 2010. I have a database that consists of bookings, receipts, clients and so on. I have made a form for booking tickets. The first field I have on my form is a combobox where a user can choose the username. And the second (receiptID) field should be filled automatically after the user has chosen appropriate username. I have an idea how to fill that field but what i don't have are the skills.
My Receipts table has these fields: receiptID, userID, cost, stateID, workerID
receiptID - Primary key
userID - Foreign key that points to the user this receipt is linked with
cost - shows the total cost of all bookings connected to this receipt
stateID - shows the current state of this receipt(1-in creation,2-created, 3-paid, 4-cancelled)
workerID - points to the accountant that signs the receipt

What i want to do is to fill the receiptID field on the form this way that if the selected user has any receipts in "in creation" state then the receiptID of that receipt is put there, but if there is no such receipt then new receipt is created and its receiptID is put there. I think I have to use SQL query in VBA to find the receiptID that satisfies the given conditions and if there aren't any results then new record will be created into Receipts table. I managed to create new record using VBA but at the moment every time I make a booking new record is created into Receipts table and that's not what I intended to do.
This is what I have right now for creating new record in Receipts table.

 
    Set dbTeater = CurrentDb
    Set rsReceipts = dbTeater.OpenRecordset("Receipts")
    rsReceipts.AddNew
    rsReceipts("userID").Value = Me!Client
    rsReceipts("stateID").Value = 1
    rsReceipts.Update
 

Query looks like this:

    SELECT Receipts.receiptID
    FROM Receipts
    WHERE Receipts.userID=Forms!BookingsF!ClientBox AND Receipts.stateID=1
 

[nico5038]

First I would like to know or there's always only one "In creation" Receipt for a customer, as it's impossible to do this "single select" in such a case.

Personally I would probably create a linked subform that will show all receipts of the selected customer ordered by ascending status and ReceiptDate (missed that field from your design, but would be usefull.

[tujurikkuja]

Well that's why my original idea seemed so good that there is always only one receipt in state "in creation" for each client. Because if there isn't any, new one will be created. But if there is one then this receiptID is being used until its state will be changed to something else and then it means that new receipt has to be created...

[nico5038]

OK, you would need the code to check for finding a record.
That can be done using the EOF (End Of File) property of the recordset like:

    Set dbTeater = CurrentDb
    Set rsReceipts = dbTeater.OpenRecordset("Receipts")
    if rsReceipts.eof then
       rsReceipts.AddNew
       rsReceipts("userID").Value = Me!Client
       rsReceipts("stateID").Value = 1
       rsReceipts.Update
    endif
 

So when the query returns no "1" record, a record will be added.

[tujurikkuja]

This code is missing the part that checks for a record that is in state "1". This far I got by myself. The real problem was how to check the properties of a record.

[nico5038]

I thought that receipts was the name of the query from your original post.
So just save the query e.g. named qryInCreationReceipts and use this queryname for the OpenRecordset.

[tujurikkuja]

Can you use eof for finding out if the recordset is empty?

[nico5038]

Yes, for 100% you could use a test for EOF and BOF (Begin of File), but I always use just EOF to check for an empty recordset after opening.

[nico5038]

To make the solution more flexible you could dynamically fill the recordset with the SQL query string and replace the Forms!BookingsF!ClientBox with concatenating the formfield: Me.ClientBox like:

 "SELECT Receipts.receiptID FROM Receipts WHERE Receipts.userID=" & Me.ClientBox & " AND Receipts.stateID=1"

[tujurikkuja]

Okay. I'll give that a try. But the query has to take one attribute from combobox. Doesn't that affect the result? Can I save that kind of query as a query in Access? I mean does Access let you save a query that includes a reference to form object.

[nico5038]

The value from the querybox will always be one or NULL.
You could test the value to be NOT NULL, but in general a value will be filled when you use the AfterUpdate event for your code.

[tujurikkuja]

Don't understand what you mean by querybox.

[nico5038]

Oops, should read "combobox" :-)

[tujurikkuja]

Well the combobox value should be from 1 to n.

[tujurikkuja]

My Clientbox should give values from 1 to n. So simply 0 and 1 are out of question. Sorry for double post...

[nico5038]

The client combobox should give a valid ClientID. When the combo isn't filled (nothing selected) the value NULL will be passed.

[tujurikkuja]

Ooh I understood now what you meant. Not the values are "0" and "1" but the query should give 0 values or 1 value.

[nico5038]

NULL and zero are different in a database environment:
0 = number indicating nothing
NULL = unknown

[tujurikkuja]

Finally got a decent solution. It was DLookUp function. Got everything done in 2 minutes after i found out how to use that and what was the syntax.