November 15, 2004 at 5:19 am
The subject was perhaps hard to understand, but I will try to explain what I'm after. This is a hotel reservation system written in SQL Server with Access as Front End.
Basically, I have a trigger that checks two fields (both datetime) in an Access form. When both fields are not equal 'NULL', the trigger will run a procedure that will write out a list of rooms available in between the dates. For the record, this works in SQL server query. I'll write the basic principle of the trigger here to give a better picture:
CREATE TRIGGER check_available_rooms ON Reservation
ON INSERT, DELETE, UPDATE -- We are unsure if this is correct for the task
AS
DECLARE @Startdate varchar(20)
DECLARE @Enddate varchar(20)
-- arrival on the reservation you are registering:
SELECT @Startdate = ArrivalDate FROM INSERTED
-- departure on the reservation you are registering:
SELECT @Enddate = DepartureDate FROM INSERTED
IF (@Startdate <> 'NULL' AND @Enddate <> 'NULL')
BEGIN
EXEC print_available_rooms @Startdate, @Enddate -- procedure works
END
In access I wanted a combo box which automatically lists the available rooms when the Arrival and Departure fields have values, before the rest of the fields in the form are filled. Instead, what happens is that you have to press the combo box and type in the dates that it asks for even after you have typed in values in arrival and departure. I have a theory that this is because the values filled in arrival and departure not yet are stored in the database, because the rest of the forms' fields are yet to be filled. There might be a way to do this with the "on exit" procedures on fields in Access, but I have no experience with Visual Basic, and I hope to resolve this in SQL Server.
Thanks in advance for any answers, suggestions or ideas on where to find the answer.
November 15, 2004 at 6:26 am
How are your access forms bound to your (presumed) adp project?
I would, anyway, recommend firing a stored procedure from access on the afterupdate event of both date fields on your access form with the following logic:
step 1: create a module to return available room numbers with a start and end date (use your proc "print_available_rooms" (put some check criteria to exit the module if both dates are not available (saving sql the trouble of processing the query) and return some useful statement back to a text field, stating something like "Please enter start and end date of reservation to view availble rooms.")
step 2: call the procedure on the afterupdate event of your start and end date fields.
Also make sure you understand the difference btw the different types of triggers, you wouldn't want your sql server to be working too hard due to unnecessary processing of triggers.
Good luck
Max
Max
November 16, 2004 at 3:01 am
Hi
Are you sure this is a SQL-Server problem.... it sounds to me like it might be that you need to do a REFRESH of the combo box values. It's a long time since I did anything serious in Access but one thing I remember is that combo boxes (and list boxes) don't "auto-refresh" when the underlying data changes... you need to make it happen.
Same sort of thing in VB by the way.
Sam
November 16, 2004 at 7:13 am
controlname.requery is the syntax for getting the combobox to requery and grab the new data.
A trigger is not the way of doing what you are wanting to do. The trigger has not fired until you commit the record to the database.
If you are using an MDB, you can requery the combobox based on the dates you have already entered if you base the SQL statement on the fields on the form. Then you will get the information you want.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply