Bizarre problem when creating bookings on events database

[JeffBarker]

Hello all,

I'm not 100% sure on the best way to explain this problem, but we run a number of events throughout the year, and each one has its own MS Access 2003 "bookings" database, where you can add new sites (companies), contacts etc.

We have a main form where you search on site name, and once you've found what you're looking for you double-click on its name to go to the site information form. This form is bound to the site information table and details Address, Telephone Number etc, with a (continuous) contacts subform that displays all known contacts that we have attached to that site. It's linked to the main form by Site ID, which is the unique identifier we use for our sites.

The query behind the subform has the Site Contacts table linked to tblMain, which holds particular details relevant to the event booking (booking type, if they're staying at a hotel, how many nights etc) and the booking reference from tblMain is also displayed on the subform. By clicking into the booking ref on the subform, you can then click on a button to add/amend a contact's booking.

This takes you to the bookings form, which is bound to tblMain (the one that holds all the booking details) and it's here that you can select the booking type (Exhibitor or Organiser, for example), what sort of of booking package they have (1 night residential, day-only etc) and other bits and pieces pertaining to that booking. Selecting the package sets all the other options to the default selection for that package, so you can't chop and change or select anything else as it's all tied in to the package you select. This then updates into tblMain, as it lists each booking reference and which package is tied to that booking.

Once that's done, you go back into the previous Site Information/Contacts form and the subform displays the new booking reference next to the Contact and highlights it green to show it's active.

Now, the problem - when adding new bookings via the bookings form, it's been creating duplicate bookings in tblMain for the same person with seperate booking numbers, when it should only be creating one. There seems to be no reason or rhyme for this to occur, as it appears to be completely random. When testing it, the system will randomly allocate one booking (as normal) or two and neither myself or AWF User Winshent are able to isolate the problem.

Does anyone have any ideas or suggestions as to why this may be happening, and how we can solve it please??

 

[winshent]

In summary..

We have a form with Company information. On this form, there is a subform listing all the contacts at the Company..

The Contacts subform is set to allow additions. When adding a new contact, there is a button which allows the user to add a new booking.

This opens a new form with the following code:

Private Sub btnBkg_Click()
  On Error GoTo Err_btnBkg_Click
      
  Dim stDocName As String
  Dim stLinkCriteria As String
  'Dim vBkgRef As Long
  
  If Dirty Then Dirty = False 'save record
  
  stDocName = "frmBooking"

  If btnBkg.Caption = "Amend Bkg" Then
    stLinkCriteria = " [BkgContactID] = " & Me.[Contact ID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria
  Else
    DoCmd.OpenForm stDocName, , , , acFormAdd
  End If
  
Exit_btnBkg_Click:
    Exit Sub

Err_btnBkg_Click:
    MsgBox Err.Description
    Resume Exit_btnBkg_Click
    
End Sub

So the booking form opens in ADD NEW RECORD mode and the user enters the booking.

For some reason this action sometimes adds two bookings in the table..

On the booking form.. this event is triggered before inserting a record into the bookings table

Private Sub Form_BeforeInsert(Cancel As Integer)
  'vp
  BkgContactID = vBkgRef
  vBkgRef = 0
    
  Me.BkgContactID = Forms![Site Information].Form![Site Contacts sub]![Contact ID]
  Me.BadgeCoName = UCase(Forms![Site Information]![Company Name])
    
End Sub

 

[gemma-the-husky]

i cant see exactly how you add the booking

i can only thing that maybe you have multiple contacts, and you are getting a cross-product that adds a booking for EACH contact.

you need to look carefully at the forms underlying query, and any code you have to add the booking, i think.

 

[JeffBarker]

i cant see exactly how you add the booking

i can only thing that maybe you have multiple contacts, and you are getting a cross-product that adds a booking for EACH contact.

you need to look carefully at the forms underlying query, and any code you have to add the booking, i think.

Hi Dave - thanks for replying.

If no booking exists then the form will open in Add mode:

  If btnBkg.Caption = "Amend Bkg" Then
    stLinkCriteria = " [BkgContactID] = " & Me.[Contact ID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria
  Else
    DoCmd.OpenForm stDocName, , , , acFormAdd
  End If

Thanks,

Jeff.