Using form to add new record

[garywood84]

I have a database which has a table of contact details. There's a separate table which contains related information about Appointments each of the contacts makes.

There are then two forms: one which allows entry/viewing of Contact details and one which allows viewing of appointments by customer. At the top of the Appointments form is a combo box that lets you select which customer's appointments you want to view.

Not all the customers who are listed in the contacts table are listed in the customer's combo box on the appointments screen, because they have not yet ever had an appointment, so don't have a record in the appointments table.

What I want to do is this: on the Appointments screen, I want there to be a button that says "New Customer". This should bring up another form which functions as a message box, in which there's a combo box listing all contacts from the Contacts table. The user should select a customer from this, then click a button. When the button is clicked a new record should be created in the Appointments table, which this customer's name in the "Customer" field. The small message-box form should then close, and the Appointments form should update to allow entry of an appointment for the newly added customer.

So far, I've got as far as creating the message-box form and adding the combo box. However, I'm not sure what code I can use to make Access add a record for the selected customer to the Appointments table, and then display that record on the Appointments form.

I hope this makes sense, since it's difficult to describe, and that somebody will be able to point me in the right direction.

Many thanks,

Gary

 

[MarkK]

One option is to create an append query in the query design grid. Get it working there using test data that your custom popup would typically collect. Then copy the SQL of that query into your popup, replacing your test data in the query with the data specified by the user. Then run that query from the popup.

This might look like ...

CurrentDb.Execute _
  "INSERT INTO YourTable ( strField1, lngField2 ) " & _
  "SELECT '" & Me.stringData & "', " & Me.longData

And if your appointment table has an autonumber ID, open the appointment form and navigate to the last record.