Autofill with a command button and prevent duplicate entries

[Harperec]

Hi,

I'm attempting to build a scheduling database, and am running into some
issues while plotting out the basics of the database by hand (easier
for me to do some basic things upfront on paper, call me crazy). I've
built a few databases so far, but they've all been much simpler in
scope. Here are the tables I currently have:

DATES
VENUE
LOCATIONS (there are several locations per venue)
CUSTOMERS
BOOKINGS

I would like to have a form per venue, with a DATES selection combo box
and a button for each location. Providing that the location is
available, I would like to be able to click the button and have it
automatically fill in the date, and location fields in the BOOKINGS
table. If that location has an entry for a particular date, I would
just like to open the BOOKINGS form to view the information.

I was thinking that the BOOKINGS table's primary key would just be an
autonumber, since there would be multiple bookings for a given date.
Would this be proper, or is there an easier way to do it?

I would imagine that I would need and If Then and If Else in a VB
script for the command button, but what is the coding to automatically
fill in fields?

Thank you very much

-eharper

 

[kerry_ja]

Hi eharper,

One way is with a SQL statement such as:

DoCmd.RunSQL "INSERT INTO BOOKINGS ( VENUE, LOCATION, DATE ) " &
txtVenue.Value & "," & txtLocation.Value & "," & txtDate.Value

Depending on what type of data is in the form, you might have to insert
quotes around it, etc.

 

[Harperec]

Kerry,

Thank you for your help. Just as a point of clairification; the fields
txtLocation, txtVenue, and txtDate just assume that the objects to fill
those fields are text boxes, correct? If they were combo boxes, for
example, they could be cboLocation. Would that be a valid assumption?

Thank you very much!

-eharper

 

[Harperec]

Kerry,

Thank you for your help. Just as a point of clairification; the fields
txtLocation, txtVenue, and txtDate just assume that the objects to fill
those fields are text boxes, correct? If they were combo boxes, for
example, they could be cboLocation. Would that be a valid assumption?

Thank you very much!

-eharper

 

[kerry_ja]

Hi eharper,

Correct, that was an example, the names of your objects may be
different. If they were combo boxes, than the names you gave as an
example would be better names. Of course the type of object does not
mean the name has to start with anything in particular, but it is
better if it does. (A combo box could be named txtLocation, but that
is a poor choice for a name, since it leads to confusion as to what
type of object it is).