Not really sure how to use my good friend google to get help with this, so thought I'd ask you educated types for a bit of advice.
As it stands at the moment, I have a table with 5 fields:
'ID' (the primary key)
All the information that's needed is already in the table, with the exception of 'Serial Number', which is to be entered by the end user.
The tricky part is, the way the form needs to be setup, it needs to have a date manually typed in, then, in a list box or combo box, it needs to list all the order numbers associated with that date within the table, and no others.
Then the user can type in a serial number, which will be added to the correct record within the database.
What would be the easiest way to go about implementing this type of dynamic form setup? Some links to guides would be useful, or just some old fashioned advice with an example.
What I would do is create a main form with the date and the combo box on it. Then I would create another form that can be added to the main form as a subform.
When the user types the date, verifiy that the date is a valid date, then set the combo box's recordsource to a SQL statement that limits the output to those order that were created on that date.
Thenactivate the combo box and after the user selects a valid order number set the subform's recordsource to some SQL that will limit the data to the order number. So the subform will only show the order's serial number.
Private Sub orderDateConfirm_Click()
Dim rs As DAO.Recordset
Dim inpODate As Date
Dim ids As Collection
Set ids = New Collection
Set rs = CurrentDb.OpenRecordset("SELECT * FROM main")
inpODate = Me.orderDateInput.Value
'Iterates through the database 'main' looking for any references to the date provided
Do While Not rs.EOF
Dim tblODate As Date
tblODate = rs.Fields("Order Date").Value
Dim curID As Long
curID = rs.Fields("ID").Value
If (tblODate = inpODate) Then
For Each i In ids
Me.debugBox1.Caption = (Me.debugBox1.Caption & i & ", ")
'Shows the order number combo box, hides the confirm button, and locks the date input
'Or, presents an error informing the user that the date given cannot be found.
If (ids.Count > 0) Then
Me.orderNumLabel.Visible = True
Me.orderNum.Visible = True
Me.orderDateInput.Locked = True
Me.orderDateConfirm.Visible = False
Else: MsgBox ("The date you have entered: " & vbCrLf & vbCrLf & vbTab & inpODate & _
vbCrLf & vbCrLf & "Appears to be incorrect. Please check it and " & _
"try again." & vbCrLf & vbCrLf & "If you are certain this is the " & _
"correct ORDER date, please see an administrator for assistance.")
Is what I have so far, and seems to be working alright. However, I now have (as I'm sure you can see from reading the code) the Collection object 'ids' populated with the primary keys for the records that are relevant to the date entered by the user.
As I'm new to VBA, access and SQL (only been using it a couple of days), I'm not sure how to use these primary keys properly.
I know I can iterate over every record, using the same method I used above in my while loop to pick out each "ID" field that matches the primary key, but is there a more efficient way of doing that? Or should I just requery the recordSet with something like:
SELECT ID FROM main
To reduce the amount that it would have to iterate over? Or is there a special command that just searches the primary keys really quickly?
Eventually this table could have well over 50,000 records in it, and I thought the whole point of having a primary key was for really quick data recovery.
Can anyone point me in the right direction for this?
I need to populate the combo box I make visible using:
Me.orderNum.Visible = True
With the information from its field "Order Number", for each record with a primary key that's in 'ids'.
Also, ideally I don't want a massive list of 100's of the same order number, but I think I can handle that. So long as when the order number is chosen the 'ids' collection (or a new collection) only retains each of the primary keys associated with the records that have the given date, and chosen order number...
Does that make sense? I've got a few other things I need to take care of today anyway, but as forums tend to be pretty slow going, I thought I'd post first thing in the hopes of getting more replies.
Wow! Being new to Access I think you might be over thinking things a little. Access controls in general are SQL aware. So you can use an SQL statement to get the data that you want into a combobox, listbox, etc.
For the code I was thinking of something a little more simple. First off, checking to see if the date was valid only meant making sure it was a date. You can a textbox to the form and then use an Input Mask to force the user to enter a proper date. Access will make sure the date is an actual date.
Then once you have this value you can use it in an SQL statement that you can use to populate your combobox. For example:
strSQL = "SELECT ID FROM main WHERE [Order Date]=#" & Me.orderDateInput & "#"
Me.orderNum.RowSource = strSQL
What that will do is show all of the IDs in the combobox for that date. I am not quite sure what is in your main table so this might need to be tweaked.
Now if you have a 'hidden' number and a user friednly order number then you can have both in the combo box. By changing the SELECT statement to include the other number the user can be shown both numbers or just the user friendly number. Is that what you mean by the Primary Key versus the Order Number?
I was very suprised to see the amount of code you had written. Typically someone that is new to Access is also new to programming as well. I don't mean to step on your toes but if you are just starting out I would recommend a couple of things with your code:
1. Use ADO instead of DAO (If you are using Access 2000 or later) DAO is an older technology that may eventually go away.
2. With recordsets, only select the fields that you need to look at. Selecting * from a table will be slower since you need to load all of the additional data into memory.
3. Use an array versus a collection to store simple data values. More overhead with a collection than an array.
I'm not new to programming really. I'm just usually a Java, and to a lesser extent C/C++ programmer. Personally, I would've just designed a DB from scratch, but my client wanted an access one so that their IT people could modify it (extra reports etc) at a later date.
Thanks for the advice in regards to the Collections and recordSet info, I just looked up briefly how to create an array in VBA and 'Collection' was the first thing I came across.
I don't really want to use that SQL to locate the ID for the date though. The end user is liable to get it wrong, and I need some method of trapping that to ensure the integrity of the system.
Is there any way to use a similar piece of SQL code, but modified slightly.
So something like this:
strSQL = "SELECT [Order Number] FROM main WHERE [ID]=#" & , , ,  & "#"
I don't seem to be able to select from multiple WHERE arguments is all. Am I missing something, or is that not possible?
The  are used in a SQL statement (in Access) to make sure a fieldname with a space in it is not misunderstood. You should be OK with ID without the . Also # is for telling Access that there is a date between the #s.
For your SQL I would probably do:
strSQL = "SELECT [Order Number] FROM main WHERE ID IN (1,5,7,100)"
Or using your code from earlier I think this is what you are looking for:
strSQL = "SELECT [Order Number] FROM main WHERE ID IN (" & Me.debugBox1.Caption & ")"
I would probably put the string of Order Numbers in a different variable for the real code.
It's just a pain going from languages you've worked with for over half a decade, into something else, and having to learn little bits of syntax, without wanting to read through 200 pages of what a loop or a static variable is.
Thanks a million for your help buddy.
I think I'll be alright from here on *touches wood*, but I know where to hola if I run into more trouble down the line!