Unable to add a new record before update

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi Experts!

I have main form which I use as a data entry and a subform in my
application. Whenever I chose to move to existing record of the form it works
fine and gives me an appropriate record in the subform.However I am unable to
create a new records. It looks like I need to add some piece of code under
BeforeUpdateEvent of the form property.

how can I do it?

Thank you in advance,
 
Have you defined primary keys in the tables and defined a relationship
between the two table? For example you have table invoice with a primary key
'Invoice No' and table invoiceitems in which 'InvoiceNo' is a foreign key.
Define a relationship between the tables ('Tools-Relationships'). Link the
form and sub form using the InvoiceNo field. When you create a new record in
the form then the add record buffer in the sub form should appear (ie. it is
all set to enter a record in table Invoiceitem with InvoiceNo from the main
form)
Hope that makes sense.
If this is not the case (most likely if the main form is unbound) then you
will need to write an append query to add records according to the criteria
specified on the main form and as you say write code to run the query when
the main form is updated. If this is your case then I can give you an example
of code, however my money is on the first!

Good luck
 
Alan,
Thank you for your response. As long as the application works for the
existing records I assume that what you said next is the case. So...could you
help me with this kind of append query? And also... were should I locate the
code? Under BeforeUpdate even procedure?

Thank you very much !
 
Alan,

Actually answering your question...no I did not defined primary keysin the
tables but I did set relationship between tables. Do you think it is
critical? Should I?
As I already mentioned , the application works fine with existing records.....
 
I One more update..

In my Access DB, I have a data entry form as the main form which includes
‘HospName’ and other information. The ‘HospName’ was selected from a list in
the main form. I also created a subform linked to the main form by ‘HospName’
which I want to show all the entered record for the selected ‘HospName’
whenever the data entry person click the list from the main form. Both main
form and subform are linked to the same table. When I choose the existing
record of the form it works fine -- subform gives me all the record for the
selected ‘hospname’. But when I go to a new record and select a ‘hospName’,
nothing shows up in the subform. I think it is because the record in Access
is updated only after you go to next.

Hopefully it is not a problem that my form and subform are linked to the
same table?
 
A sub form is used to display data records in tables related to the main form
not in the same table. If the data is all in one table then it can all be
displayed on the main form. When the data entry person enters the Hospname in
the field on the main form the rest of the record stays blank until the data
entry person enters data and saves it.

I'm not quite sure what exactly you want to do but...
If all the data is in one table display it all on the sub form.
OR
If there is a set of details attached to each Hospname which is the same for
every record involving a particular hospname then make a separate table with
an entry for each Hospname related to the first table by hosp name and
display that table on a subform.

For example if your current table (patients) has fields:
Patientname
Patientaddress
BirthDate
Hospname
Hospaddress
Hospphone

Then you should make two tables because each Hosp can have many Patients
Tables would be.

Patients with fields PatientName, PatientAddress, Birthdate, Hospname

Hosps with fields Hospname, Hospaddress, Hospphone.

The two tables would be related by Hospname.

On your form you would include a subform Hospdetails with Child and
Masterlinked fields = Hospname

If the data entry person entered a Hospname which had not been used before
then a new blank record would appear in the sub form.

Hope this is helpful, I have made a lot of assumptions!

Alan
 
Back
Top