Car hire database

[lolosuarez87]

Hi, I'm currently having a problem with my car hire database in access. I want to avoid duplicate booking for the same car number plate between dates, and I don't know how to!

The 2 tables involved in this are reservations and cars.
The cars table has:
num_plate
make
model
colour
engine

And the reservation table has:
reser_ID
date_reserved
date_back
num_plate
days

Could anyone help on how to avoid duplicate reservations between dates.

 

[jdraw]

This info and picture may help you.

 

[pbaldy]

Approved both posts, as they were moderated.

 

[lolosuarez87]

Thanks but i think that doesnt answer my problem.

Couldnt i use a condition with iif??

Something like iif num_plate between dateout and datein = 1
Then msgbox ("car is already hired for these dates")

Could someone help with the coding as i am pretty new with sql!

Thanks

 

[Solo712]

Thanks but i think that doesnt answer my problem.

Couldnt i use a condition with iif??

Something like iif num_plate between dateout and datein = 1
Then msgbox ("car is already hired for these dates")

Could someone help with the coding as i am pretty new with sql!

Thanks

Hi,
create a CarList listbox from your cars table with the LicPlate as a bound column. Then you can check the availability of the car by clicking on the list generating an event, like this:

Private Sub CarList_Click()
      Dim o As Integer
 
      o = DCount("ID", "Reservation", "LicPlate = '" & CarList & "' AND date_reserved <= #" _
                      & Format(txtDateBack, "mm/dd/yyyy") & "# AND date_back >=  #" _
                      & Format(txtDateReserved, "mm/dd/yyyy") & "#")
 
      If o > 0 Then
          MsgBox "Car already hired for those dates !"
     Else
          MsgBox "Car is available !"
     End If
End Sub

Good luck with your project !

Best,
Jiri

 

[lolosuarez87]

Hello!

the file won't open :banghead:

 

[vbaInet]

Have you tried what Solo712 wrote in post #5?

 

[lolosuarez87]

Yes, ive tried that and It keeps giving me errors saying the table which im referring to does not exist when it does... Does anyone have an example database of a dvd store or library where it prevents duplicate bookings??? Ive been trying for dsys and cant get it to work

 

[pbaldy]

If you post your attempt, perhaps we can see why you get an error.

 

[Solo712]

If you post your attempt, perhaps we can see why you get an error.

The one thing I noticed is that when I created the mockup I called the ID field "ID". In his naming it is "reser_ID". But would that be a problem one gets stuck on for days ?

Best,
Jiri

 

[lolosuarez87]

Ok so I wrote it in English for simplicity but my actual database is in Spanish, but this wont change anything...

My table for reservations is "Reservas",
Fields involved in this are num_reserva ( reservation number ), matricula (number plate), fecha_reserva (reservation date), fecha_recogida (reservation date back)
I've created my car listbox and I generate the following event:

Private Sub Lista21_Click()
Dim o As Integer

o = DCount("Reserva", "matricula = '" & Lista21 & "'' AND fecha_reserva <= #" _
& Format(txtfecha_recogida, "mm/dd/yyyy") & "# AND fecha_recogida >= #" _
& Format(txtfecha_reserva, "mm/dd/yyyy") & "#")

If o > 0 Then
MsgBox "Car already hired for those dates"
Else
MsgBox "Car available"
End If
End Sub

The error that pops us when I click on number plate 0035-GTL for instance states "Microsoft access cannot find a table or query with entry 'matricula='0035-GTL' AND fecha_reserva <= ## AND fecha_recogida >= ##'. Make sure it exists and the name is written correctly.

 

[lolosuarez87]

Ok... I think im getting somewhere now...

I have made a query to Find out the cars hired out within a date range...

The query SQL is as follows:

for understanding - matricula means number plate, fecha entrega means date of delivery and fecha devolucion means date back

SELECT Reservas.matricula, Reservas.fecha_reserva, Reservas.fecha_recogida
FROM Reservas
WHERE (((Reservas.[fecha_reserva])<[Fecha Devolucion]) AND ((Reservas.[fecha_recogida])>[Fecha Entrega]) AND ((Reservas.[matricula])=[Inserte Matricula]))

This works but now I need to add a DCOUNT And IF function so that when I get a result for this query, a msg pops up not allowing the user to enter the record.:banghead:

Could you guys help me with this?

Thanks,
Much appreciated
Manuel

 

[vbaInet]

That's not what you were given.

So going back to that code, use one of the following:

    o = DCount("*", "Reserva", "matricula = '" & Me!Lista21 & "' AND " & _
                               "fecha_reserva <= #" & Format(Me.txtfecha_recogida, "mm/dd/yyyy") & "# AND " & _
                               "fecha_recogida >= #" & Format(Me.txtfecha_reserva, "mm/dd/yyyy") & "#")

    
    o = DCount("*", "Reserva", "matricula = '" & Me!Lista21 & "' AND " & _
                               "fecha_reserva <= " & Format(Me.txtfecha_recogida, "\#mm\/dd\/yyyy\#") & " AND " & _
                               "fecha_recogida >= " & Format(Me.txtfecha_reserva, "\#mm\/dd\/yyyy\#"))

                               
    o = DCount("*", "Reserva", "matricula = Forms![COLOR="Blue"]FormName[/COLOR]!Lista21 AND " & _
                               "fecha_reserva <= Forms![COLOR="blue"]FormName[/COLOR]!txtfecha_recogida AND " & _
                               "fecha_recogida >= Forms![COLOR="blue"]FormName[/COLOR]!txtfecha_reserva")

For the third one, substitute the bits in blue with the name of the form

 

[Solo712]

Ok so I wrote it in English for simplicity but my actual database is in Spanish, but this wont change anything...

My table for reservations is "Reservas",
Fields involved in this are num_reserva ( reservation number ), matricula (number plate), fecha_reserva (reservation date), fecha_recogida (reservation date back)
I've created my car listbox and I generate the following event:

Private Sub Lista21_Click()
Dim o As Integer

o = DCount("Reserva", "matricula = '" & Lista21 & "'' AND fecha_reserva <= #" _
& Format(txtfecha_recogida, "mm/dd/yyyy") & "# AND fecha_recogida >= #" _
& Format(txtfecha_reserva, "mm/dd/yyyy") & "#")

If o > 0 Then
MsgBox "Car already hired for those dates"
Else
MsgBox "Car available"
End If
End Sub

The error that pops us when I click on number plate 0035-GTL for instance states "Microsoft access cannot find a table or query with entry 'matricula='0035-GTL' AND fecha_reserva <= ## AND fecha_recogida >= ##'. Make sure it exists and the name is written correctly.

It looks like you are missing the first argument: most likely, the expression should be:

o = DCount("num-reserva", "Reservas", "matricula = '" & Lista21 & "'' AND fecha_reserva <= #" _
& Format(txtfecha_recogida, "mm/dd/yyyy") & "# AND fecha_recogida >= #" _
& Format(txtfecha_reserva, "mm/dd/yyyy") & "#")

For the future, when you come to AWF for help, do let us know how the solutions we have supplied worked for you. Thanks.


Best,
Jiri

 

[JLCantara]

Hello!

Now you get the solution with proper attachment!!!

Good luck, JLCantara

 

[lolosuarez87]

Great! It works now!! Thanks so much guys &#55357;&#56836;&#55357;&#56836;&#55357;&#56397;