Adding records with a form

  • Thread starter Thread starter Sara
  • Start date Start date
S

Sara

I've had a little help with my current project so far,
thank you. Now we have a need to add records, and I'm
stuck. Here's the situation.

I have a form that shows employees from the Employee
table. On it I have a subform that displays related
employee records - Employee Charges (related by EENum). I
can select a record and edit - just fine.

My problem is when I want to ADD a new Charge record. I
have an "Add Charge" button on the main form. When the
user clicks that, I want to open a form "Add Charge" and
pre-fill the form with Employee Number, Name (from the
Employee Table) and Date (I use yesterday - all set there)
and Location. I take Location from the latest record on
the subform (Employee Charges). Date and Location do not
appear on the Employee Table, only on Employee Charges.

My problem is that when I open the Add Charge form, I
can't get the information to show up. I declare all my
variables, then use code (with a MsgBox first to see the
criteria for testing - criteria is correct) to open the
form. I think it might be something to do with the Data
Source for the form, but I've had no luck figuring it out
with Help, Northwind and trial and error.

Here's the code:
DCL (all variables declared)
sDoCmd.OpenForm "F_AddCharge",,,stlinkCriteria.

StlinkCriteria, for one example, is Employee 1084, Date
8/14/04 and Location 12.

Any ideas where I went wrong?
Many thanks.
Sara
 
Sara,

You are trying to open a form at a non-existent record. In your
example, you are trying to open the F_AddCharge form, at a record that
meets your criteria, i.e. where Employee is 1084, Date is 14-Aug-04 and
Location is 12. Am I right? But there is no such record, because it
hasn't been created yet.

Any reason for not just entering the new Charge record on the subform?
 
You are right - I am trying to open a form with a record
that doesn't exist. I had figured this was the problem,
but don't know how to fix it.

The only reason not to add the form on the subform is that
I display the records in REVERSE chronological order and
the "ADD" is at the end. I could teach the users how to
click to the end, I suppose that's easy enough, but it's a
little confusing with the dates.

Also, I do NOT want them to be able to CHANGE ANYTHING on
an existing record on the subform. (I still have to
figure out how to do this, but..) Anytime there's a change
in an existing record, I have to write the ORIGINAL,
UNCHANGED record to a table (to save the history trail),
and write just the CHANGED record to the Employee Charges
Table.

I am afraid that if I allow the records to be added in the
subform I will (by default) be allowing changes to
existing records and I can't have that. Is that correct?

Thanks for the help. Much appreciated.
Sara
 
You are right - I am trying to open a form with a record
that doesn't exist. I had figured this was the problem,
but don't know how to fix it.

The only reason not to add the form on the subform is that
I display the records in REVERSE chronological order and
the "ADD" is at the end. I could teach the users how to
click to the end, I suppose that's easy enough, but it's a
little confusing with the dates.

Also, I do NOT want them to be able to CHANGE ANYTHING on
an existing record on the subform. (I still have to
figure out how to do this, but..) Anytime there's a change
in an existing record, I have to write the ORIGINAL,
UNCHANGED record to a table (to save the history trail),
and write just the CHANGED record to the Employee Charges
Table.

I am afraid that if I allow the records to be added in the
subform I will (by default) be allowing changes to
existing records and I can't have that. Is that correct?

Thanks for the help. Much appreciated.
Sara
 
Sara,

If you set the AllowEdits property of the form that is used as the
subform to No, the users will not be able to change existing records.

One approach that is sometimes used to overcome the "new records at the
bottom" problem, is to have 2 subforms, both based on the same
table/query. One of them, such as the one you've got, you set the
AllowAdditions property to No. The other one, place on the main form
above the other one, set it up so you can only see one row (in fact with
a bit of fiddling you can make it look like it is part of the other
subform, with a blank row at the top), set the Data Entry property to
Yes, and on its After Update event use a simple procedure to go to a new
record itself, and Requery the first subform. Hope you understand the
general concept of what I mean.
 
Back
Top