By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,121 Members | 1,706 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,121 IT Pros & Developers. It's quick & easy.

How to use SQL query in VBA function?

P: 13
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.
Expand|Select|Wrap|Line Numbers
  1.  
  2.     Set dbTeater = CurrentDb
  3.     Set rsReceipts = dbTeater.OpenRecordset("Receipts")
  4.     rsReceipts.AddNew
  5.     rsReceipts("userID").Value = Me!Client
  6.     rsReceipts("stateID").Value = 1
  7.     rsReceipts.Update
  8.  
Query looks like this:
Expand|Select|Wrap|Line Numbers
  1.     SELECT Receipts.receiptID
  2.     FROM Receipts
  3.     WHERE Receipts.userID=Forms!BookingsF!ClientBox AND Receipts.stateID=1
  4.  
Apr 15 '12 #1
Share this Question
Share on Google+
18 Replies


nico5038
Expert 2.5K+
P: 3,072
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.
Apr 15 '12 #2

P: 13
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...
Apr 15 '12 #3

nico5038
Expert 2.5K+
P: 3,072
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:

Expand|Select|Wrap|Line Numbers
  1.     Set dbTeater = CurrentDb
  2.     Set rsReceipts = dbTeater.OpenRecordset("Receipts")
  3.     if rsReceipts.eof then
  4.        rsReceipts.AddNew
  5.        rsReceipts("userID").Value = Me!Client
  6.        rsReceipts("stateID").Value = 1
  7.        rsReceipts.Update
  8.     endif
  9.  
So when the query returns no "1" record, a record will be added.
Apr 16 '12 #4

P: 13
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.
Apr 16 '12 #5

nico5038
Expert 2.5K+
P: 3,072
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.
Apr 16 '12 #6

P: 13
Can you use eof for finding out if the recordset is empty?
Apr 16 '12 #7

nico5038
Expert 2.5K+
P: 3,072
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.
Apr 16 '12 #8

nico5038
Expert 2.5K+
P: 3,072
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:

Expand|Select|Wrap|Line Numbers
  1.  "SELECT Receipts.receiptID FROM Receipts WHERE Receipts.userID=" & Me.ClientBox & " AND Receipts.stateID=1"
Apr 16 '12 #9

P: 13
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.
Apr 16 '12 #10

nico5038
Expert 2.5K+
P: 3,072
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.
Apr 16 '12 #11

P: 13
Don't understand what you mean by querybox.
Apr 16 '12 #12

nico5038
Expert 2.5K+
P: 3,072
Oops, should read "combobox" :-)
Apr 16 '12 #13

P: 13
Well the combobox value should be from 1 to n.
Apr 16 '12 #14

P: 13
My Clientbox should give values from 1 to n. So simply 0 and 1 are out of question. Sorry for double post...
Apr 16 '12 #15

nico5038
Expert 2.5K+
P: 3,072
The client combobox should give a valid ClientID. When the combo isn't filled (nothing selected) the value NULL will be passed.
Apr 16 '12 #16

P: 13
Ooh I understood now what you meant. Not the values are "0" and "1" but the query should give 0 values or 1 value.
Apr 16 '12 #17

nico5038
Expert 2.5K+
P: 3,072
NULL and zero are different in a database environment:
0 = number indicating nothing
NULL = unknown
Apr 16 '12 #18

P: 13
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.
Apr 28 '12 #19

Post your reply

Sign in to post your reply or Sign up for a free account.