check if there are available slots and then if not prompt user

[ctrl]

Hi! I want to know what module / vba code should I do to achieve this effect:

I'm making a train reservation database. For this problem, I have three fields: slot_number which is supposed to be an autonumber, schedule_date which houses the date which the users can choose, and then schedule_time which should be a drop-down menu and the user can choose between A and B.

These three is a bit related, like... what I want to happen is if the user chooses a date or puts a date in schedule_date and then chooses time (A or B) in schedule_time, so let's say user chooses A, then click a command button "verify", the database will allow the entry / entries to the other fields if slot_number hasn't reached a certain number (example, 100) for that schedule_time (A) and date. If the database finds that slot_number has reached a certain number it will not allow putting entries to other fields and then a message box will appear saying "Limit reached. Choose another time or date).

I'm new and I've been trying to look for solutions but I can't come up with the right one. Please help me and thank you!

 

[arnelgp]

put this on your combobox Change event:

if Nz( DCount("*","yourTrainReservationTableNameHere", "[schedule_date] = #" & Format([yourTextBoxDateHere],"mm\/dd\/yyyy") & "# And [schedule_time] = #" & yourComboBoxTimeHere & "#")) >= 100
'then limit of reservation reached
Msgbox ""Limit reached. Choose another time or date"
Me.Undo
End If

 

[ctrl]

Hello arnelgp! Thank you for your reply!

I'm getting an error. Here's what I placed:

Private Sub Command26_Click()
if Nz( DCount("*","ReservationTable", "[schedule_date] = #" & Format([Schedule_Date],"mm\/dd\/yyyy") & "# And [Schedule_Time] = #" & Combo20 & "#")) >= 100 /* this part goes red */
'then limit of reservation reached
Msgbox "Limit reached. Choose another time or date"
Me.Undo
End If
End Sub

 

[arnelgp]

where does your drop down menu getting its data (Rowsource that is).

 

[ctrl]

I entered the values. Rowsource for Combo20 is "A: 8:00AM";"B: 5:00 PM" , then control source is set to schedule_time.

 

[arnelgp]

Private Sub Command26_Click()
' "A: 8:00AM";"B: 5:00 PM"
'
' we have to extract the time from the string.
'
dim aTmp() as string
dim strTime as string
dim i as integer

aTmp = split(Combo20, ":")

For i = 1 to UBound(aTmp)
strTime = strTime & aTmp(i) & Iif(i = 1, ":", "")
Next i

if Nz( DCount("*","ReservationTable", "[schedule_date] = #" & Format([Schedule_Date],"mm\/dd\/yyyy") & "# And [Schedule_Time] = #" & strTime & "#")) >= 100
'then limit of reservation reached
Msgbox "Limit reached. Choose another time or date"
Me.Undo
End If
End Sub

 

[ctrl]

Thank you again, though I'm still getting an error in if Nz( DCount("*","ReservationTable", "[schedule_date] = #" & Format([Schedule_Date],"mm\/dd\/yyyy") & "# And [Schedule_Time] = #" & strTime & "#")) >= 100 as it appears red.

I attached two screenshots to this post.

I'm sorry I really dont know what to do T_T

 

[arnelgp]

is it possible for you to upload a strip down version of your db?

 

[ctrl]

Here's the accdb file of the database i'm trying to work on. i hope this does help you to figure out what's wrong. thank you very much for trying to help me.

 

[arnelgp]

kabayan ka pala

here try this:

 

[ctrl]

opo! sorry po late reply. thank you po talaga for trying to help me.

Yes, there are no errors now though I still got a bit of a problem. I tested if the code works (well, I placed 2 instead of 100) and when I tried running the form, it did not " "verify" (i.e., no message box or anything).

 

[ctrl]

Sorry for the double post, but what does this do?

aTmp = Split(Combo20, ":")

For i = 1 To UBound(aTmp)
strTime = strTime & aTmp(i) & IIf(i = 1, ":", "")
Next i