Insert record into other table from form (1 Viewer)

Access 2007

In order to better design the data structure/table design, I need the ability to add a new record to a table based on data from a form as well as a value that is not in the form's table.

For example:
I am entering patient information (ID, name, birthdate, address, etc.). This patient may be admitted and discharged multiple times at one or more hospitals, so I need another table that will have PatientID, HospitalID, AdmitDate, DischargeDate.

My goal is to give the user the ability to maintain that by going through the Patient form. Without using a subform, is it possible to create fields in the form and have my record insert code reference them? So, on a patient record (which would already have the PatientId from the Patient table), could I have some "floating" fields where the user could enter the admit/discharge dates so I could use them in the code as inputs to the code to insert a new record into the AdmissionHistory table?

Thanks!

smithn293 said:

Access 2007
Without using a subform
Thanks!

Click to expand...

I don't believe so.

How do you intend to show many records without a subform.

This is perfectly possible however you could easily code yourself into a corner. If its only to be used for entering a discharge date then 3 boxes with a button running the insert statement would work. You can fill them by doing lookups for the TOP1.

Why dont you look at a control that you can fill recursively ie. Treeview?

I am not sure that we are on the same track.

But You can set the properties of a form "Data Entry" to Yes

RainLover said:

I am not sure that we are on the same track.

But You can set the properties of a form "Data Entry" to Yes

Click to expand...

You can - I assume you are speaking about a subform being used? I was trying to give the OP a method to do this without a subform as requested... I may be misunderstanding you - please clarify

dcb

I really can't add anything more.

I need to hear from the OP.

Thank you both for your prompt replies. Let me take a step back to explain the concept I am trying to tackle.

I am a software consultant for a specific software (not medical- this is different effort). In my software, we have a db schema that uses tables to link data together from different tables. For example, I might have tables for Patient and Contact. In a third table, called Patient_Contact, I would create as many relationships as needed. This keeps me from having to add specific fields in Patient for ContactID_1, Contact_ID_2, etc.

So here are 2 examples of what I'm trying to do:

(1) I have a form for Patient records with subform of Contact. For a given Patient record, I would like to select a Contact record in the subform, and then have the ability to insert a record into the Patient_Contact table to create the link between the PatientID and ContactID

(2) I have a form for Patient with no subforms. Instead, I want "free-floating" fields where the user can enter a HospitalID, AdmissionDate, and DischargeDate. I then would kick off a code to update a table called PatientAdmission to record PatientID, HospitalID, AdmissionDate, and DischargeDate.

I hope this explains the concept I'm trying to figure out.

Thanks.

You only concerned about the insert for now - not the display?

Create a insert statement in code and run it.

Have a look in help for Currentdb.execute and docmd.runsql methods

So, for example #1, I just need to learn VBA I guess (I'm new to it). But the second example requires the use of fields created on a form that don't link to a table. Is that possible?

smithn293 said:

So, for example #1, I just need to learn VBA I guess (I'm new to it). But the second example requires the use of fields created on a form that don't link to a table. Is that possible?

Click to expand...

Yip you need to learn a little VBA and yes its completely possible

Let me give you a little start:



Note the """ used to create the " required in a sql statement for string type
Post back when you get stuck

Cool, thanks for the help!