Reset Field Value

[BoroLee]

Help please

I am wanting to create some code which will resest a field.

I have a form called frmEvent which lists Events being offered. Against each Event are a list of Delegates, and these delegates are picked from a list of Contacts.

What i want to do is if say Event 1 exists, and there are 91 delegates, i want the next BookingID number to be 92. If however, Event 2 is added, and there are no delegates, i want the BookingID to be 1 and increments.

Hope this makes more sense than yesterdays' attempt.

 

[ColinEssex]

Re: Help please

What i want to do is if say Event 1 exists, and there are 91 delegates, i want the next BookingID number to be 92. If however, Event 2 is added, and there are no delegates, i want the BookingID to be 1 and increments.

Hope this makes more sense than yesterdays' attempt.

So the booking ID is based on the previous events delegate number?? if so . . .why?

Am I being thick today?

As a matter of ettiquete - could you make your titles a bit more meaningful to your question, this really helps others who may be searching for a similar answer thanks

Col

 

[BoroLee]

The reason for this is that the owner of the DB wants to write a bookingID on each from for each event. they want the number reset to 1 when a new event is added.

 

[Mile-O]

Re: Re: Help please

As a matter of ettiquete - could you make your titles a bit more meaningful to your question, this really helps others who may be searching for a similar answer

Well said, Col. You also forgot to mention that it makes my life easier as I feel I have to rename these annoyingly titled posts. Here, for example are a lost of all the posts with help in the title (albeit some are legitimate). Click here - and that doesn't include all the other types as defined by myself on this thread.

 

[BoroLee]

Ok guys. point taken. will be more descriptive from now on


The other reason for wanting to reset the number back to 1 and then increment is because some events will have limited spaces. by resetting the BookingID to 1, the user can easilt see how many delegates are on the event in question.

 

[Mile-O]

Do you remember that example I gave you for people's name and making StudentID numbers? Same principle.

 

[BoroLee]

i do.

have tried playing arounf with the code, but can't get it to work at all.

still feeling my way around all this.

Sorry

 

[BoroLee]

This is the code i've got attached to the AfterUpdate Event on the field cboContact.

Private Sub cboContact_AfterUpdate()

If DCount("PersonID", "tblPersons", "EventID") = 0 Then
Me.PersonID = 1
Else
Me.PersonID = DMax("PersonID", "tblPersons", "PersonID") + 1
End If

End Sub

All i keep getting however is 0 in the BookingID field

 

[Mile-O]

Well, that code isn't doing anything to the BookingID. It's only affecting the PersonID.

 

[BoroLee]

have changed it too

Private Sub cboContact_AfterUpdate()

If DCount("BookingID", "tblPersons", "EventID") = 0 Then
Me.PersonID = 1
Else
Me.PersonID = DMax("BookingID", "tblPersons", "PersonID") + 1
End If

End Sub

but i'm still only getting a 0 in the BookingID field

 

[Mile-O]

Side A = Side B

Side A receives the value. Side B is the value being received.

Therefore, you are still just assigning a value to the PersonID. The BookingID is still not affected.

 

[Mile-O]

Can you show your table design as I'm can't really visualise what you are trying to do. The delegates part is throwing me off...

 

[BoroLee]

Ok. It's baiscally this :

PersonID, autonumber and primary
EventID, number
ContactID, number
BookingID, number

the EventID is a defualt from the prvious form, the ContactID is selected from a combo on the form, and the BookingID is the one i'm trying to resolve

 

[Mile-O]

PersonID, autonumber and primary
EventID, number
ContactID, number
BookingID, number

One table for Persons.
One table for Events.
One table for Bookings.

I'm unclear on the ContactID.
Can someone in the Persons table not be a contact? Is that the idea? Are all Persons potential Contacts?

tblPersons
PersonID
Forename
Surname
anything specific to one person ONLY

tblEvents
EventID
EventName
DateOfEvent
anything specific to one event ONLY

tblBookings
BookingID
DateOfBooking
PersonID - foreign key to tblPersons' PersonID*
EventID - foreign key to tblEvents' EventID
anything specific to one booking ONLY

tblPersonsToBooking
BookingID - foreign key to tblBookings' BookingID
PersonID - foreign key to tblPerson's PersonID
DelegateNumber

* PersonID in this case is assumed to be the Booking's Contact

Now, tell me that's what you've got already...

 

[BoroLee]

There is a tblContact, tblEvents, and tblPersons.

Each of these tables has a primary ID key.

The idea id that the tblPersons will hold the EventID, and also the ContactID from the tables.

The BookingID is only in tblPersons

 

[BoroLee]

No-one can be in tblPersons without first being in tblContacts.

Hence all delegates, must be a contact first

 

[Mile-O]

There is a tblContact, tblEvents, and tblPersons.

Please answer my question above about Contacts and Persons.

The idea id that the tblPersons will hold the EventID, and also the ContactID from the tables.

This is wrong as the Event and the Contact should not depend on the Person.

The BookingID is only in tblPersons

I may be viewing Booking as something different to you.

 

[BoroLee]

The BookingID too me is the number in order that person has booked on the specific event

 

[Mile-O]

Look at the structure I gave you again as I don't think 3 minutes between me posting it and you reading it and responding is enough time to digest it.

It's got everything you want and is properly structured. Four simple tables: Persons, Bookings, Events, and PersonsToBookings.

 

[BoroLee]

OK i understand where you are coming from.

However, don't want a PersonstoBookings tbl.

Why can't i just operate with the 3 tbls