Need a VBA guru to help solve a problem

[Zuzu]

Hello

I am trying to make a small equipment booking form. The form is unbound, and has a fair bit of vba behind it.

I have modified this from a working example that was posted to the public domain some time ago.

My problem is this:

The original form only had 10 rows in it for equipment. I have changed it to now show 20 pieces of equipment. I have managed to get new bookings to show correctly on the grid format for items 1 thru to 20..... however, i am unsure on where to change the code for the last bit of my project.

After an equipment has been scheduled, the user should be able to click on the grid on that booking, and the information for the booking will be shown on the right hand side of the form in the 'Edit Existing Booking' fields i have created.

Currently, If equipment is listed in the 1 - 10 position, then the code works (as can be seen in the db is you click on the yellow / red / blue bookings).

However, if the user clicks on a booking in rows 11 - 20, no data is appearing in the 'Edit Existing Booking' fields (nothing displays for the green booking).

How or where do i change the code to get this working?

I have attached the user guide for the original form as well.

Any help greatly appreciated.

Cheers

Zuzu

 

[JHB]

..
The original form only had 10 rows in it for equipment. I have changed it to now show 20 pieces of equipment.

The number in the control name and the number in the On Click event must be the same, so you need to go through all controls you added and correct it.
The first picture shows one of the original control, the second picture shows one you've added.

..
..Currently, If equipment is listed in the 1 - 10 position, then the code works (as can be seen in the db is you click on the yellow / red / blue bookings).

Sorry, but I can't see that works!

 

[Zuzu]

JHB

Once again... a big thank you!

This is the first time i am using an unbound form, so it has been a big learning experience for me.

You will have noticed some temporary text fields i placed on the right hand side of the form to see if i get could the data from the booking... and happy that some info was populated in there!

i am assuming now that once i get this data in the 'Existing Booking' fields, then i will have to write an update query so that the user can amend a booking detail? Hopefully i am on the right track.

Greatly appreciate the help mate!

Zuzu

 

[JHB]

JHB

Once again... a big thank you!

You're welcome, (again).

You will have noticed some temporary text fields i placed on the right hand side of the form to see if i get could the data from the booking... and happy that some info was populated in there!

I the first I didn't notice them, but now it see it.

i am assuming now that once i get this data in the 'Existing Booking' fields, then i will have to write an update query so that the user can amend a booking detail? Hopefully i am on the right track.

You can get the data into the 'Existing Booking' fields, with the below code, remember to declare rst as DAO.Recordset

 '  txtClick.Value = sUnitID
 '  txtClickDate.Value = d
 '  txtClickBookingType = sColor
   
   Set rst = CurrentDb.OpenRecordset("SELECT tblEquipment.UnitID, tblEquipment.Unit, HireDateFrom, HireDateTo, ColorKey, MemrID " _
   & "FROM tblEquipment INNER JOIN tblEquipmentHire ON tblEquipment.UnitID = tblEquipmentHire.UnitID " _
   & "WHERE tblEquipment.Unit='" & sUnitID & "' AND #" & Format(d, "mm/dd/yyyy") & "# Between [HireDateFrom] And [HireDateTo];")
   If Not rst.EOF Then
     Me.txtMemrEdit = rst![MemrID]
     Me.Text719txtHireDateFromEdit = rst![HireDateFrom]
     Me.txtHireDateToEdit = rst![HireDateTo]
     Me.cboBookingTypeEdit = rst![ColorKey]
     Me.cboEquipmentEdit = rst![UnitID]
   End If

Yes an update query would do the job.