i have a tblBooking.DateOfOrder and i want to make the date in this field not exceed the date in tblOuting.DateOfOuting
ive tried:
tblBooking.DateOfOrder<tblOuting.DateOfOuting
The second one is that i have created the database, with a tblCoachDetails and within that table there is a field called NumberOfSeatsAvailable, and what i want to do is with this is to make sure that when orders are placed, is that i dont want there to be more booked seats (tblBooking.NumberOfSeats) than avaliable.
Question #1: It sounds like your database structure may be bad. What is the relationship of tblBooking to tblOuting? If they both refer to like excursions then DateOfOuting should be in the same table/record of DateOfOrder.
Question #2: You can perform a query with a domain aggregate function to determine total number of seats taken for a particular expedition. Then compare that number to the new number of seats requested.
Both of these questions are rather indepth issues. If this "is urgent", you may want to reconsider your timeline.
The answer to the question 1 is that the relationship to the tblBooking and tblOuting is the OutingID is a primary key in tblOuting and a foreign key in tblBooking.
About question 2, im completely lost! can u be more specific because im new to access!
1. To be clear, it would be helpful to me to list the fields in each table. From your answer, I assume that each "Outing" can have multiple "Bookings".
2. On your Booking form place a textbox (txtChairsFilled) that has its ControlSource equal to a DSum function. Look up DSum in the help file. Then use VBA to validate that the booking does not seat more than TotalSeats - txtChairsFilled.
Question 1: your assumption is correct. each outing has multiple bookings! would i have to change that in order for the dates to work?
Question 2: with your advice, i will put into practise now! and i will c how it goes! btw, i don't know VBA at all, im gonna learn that soon! ive got some books, what shall i read, for that DSum function?
1. The dates should work as I understand your structure now.
2. VBA is key to making anything but the most rudimentary databases. Welcome to your endeavor. DSum info is explained in the Help file. You will need to use an If...then...else construct in VBA in order to compare number of seats already assigned. A book (some are recommended on this site) is going to be the best way to learn VBA. When you hit a roadblock, post your (specific) questions to this forum.
First, you won't be comparing information in the tables directly. It is much easier to compare textboxes on forms based on the tables involved. When you get far enough into MS Access, you will find you can create direct comparisons of tables but that is much more involved.
If you are wishing to insure that the Date of Order is earlier than the Date of Outing, try this:
In the BeforeUpdate event of txtDateOfOrder put:
If DateValue(txtDateOfOrder)> DateValue(txtDateOfOuting) then
Msgbox "The order date must be earlier than the outing date."
Cancel = True 'This prohibits the entry and places the user back in the txtDateOfOrder textbox to correct his/her mistake.
End If
You will need to develop some error handling for the code above, because the user could type a nonsensical entry (such as "J@jk2Dk"), and this will crash the DateValue function.
hey jeff, right, ive tried that in the before update for txtDateOfOrder, but the problem is that it rejects all dates, even the correct ones! what do i do now?
The SQL statement would be placed within the Before Update event of your txtName and txtDate on your frmBooking.
Note: It is good programming style (no, essential programming style) to use prefixes for tables, fields, queries, controls, etc... There are a number of suggested prefix lists out there, just pick one that you like. The utility of prefixes arises when trying to debug code. Your example...
SELECT DateOfOrder FROM tblBookings WHERE DateOfOuting = ' " & DateOfOrder & AND OutingID = & OutingID &
...is hard to decipher because I don't know if "DateOfOrder" is referring to a field or textbox.
Learning VBA is essential, and you really just have to bury your head in a book and look at a lot of sample code. There are many sample databases on this forum that you can study and pick apart.
My big time suggestion is to first develop your project as much as possible without incorporating any VBA. Create two tables (tblBookings and tblOutings) and a single form frmOuting with a subForm frmBooking. This simple database should be very functional to you even at this rudimentary level. Next, start adding short error trapping routines in VBA. An example would be "what if the user types a date that is in the past?". Finally, add more complicated error checking using VBA and SQL.
To get a taste of what VBA and SQL look like, here's a snippet from a project I'm working on:
Code:
Public Sub UpdateRecentList(lPatientID As Long)
Dim sSQL As String
Dim rsRecent As ADODB.Recordset
sSQL = "SELECT fldUserID, fldPatientID, fldDate " & _
"FROM tblUsersPatients " & _
"WHERE fldUserID = " & Forms!frmSplashScreen!txtUserID & " " & _
"ORDER BY fldDate;"
Set rsRecent = New ADODB.Recordset: With rsRecent: .CursorType = adOpenStatic: .LockType = adLockPessimistic: .CursorLocation = adUseClient: .Open sSQL, CurrentProject.Connection
.MoveFirst
.Find "fldPatientID = " & lPatientID
If .EOF Then 'If the PatientID is not in the list already...
If .RecordCount < 10 Then
.AddNew 'Create a new record if number of Recent PatientID's is not 10
Else
.MoveFirst 'Change the first record (oldest)
End If
End If
!fldUserID = Forms!frmSplashScreen!txtUserID
!fldPatientID = lPatientID
!fldDate = Now()
.Update
.Close
End With
Set rsRecent = Nothing
cmbRecent.Requery
cmbRecent = cmbRecent.Column(0, 0)
End Sub
Its not easy. It's an educational process. But most importantly, it's just fun.
