Can't save record

  • Thread starter BruceM via AccessMonster.com
  • Start date
B

BruceM via AccessMonster.com

I asked a question yesterday in the Forms Programming group, but have not
received a reply, so I am trying again with less detail and a different
subject line.

Is it possible to save a record that has no user-input values, but only
Default Values? The record in question is in the top level table. At first
it contains only a date. Later, when the subform records are processed,
there will be more data. However, as it stands subform records are orphans
because Access does not recognize a main form record, and therefore there are
no values in the linking field.

I could solve the problem by making the user input the date, or using a
command button to add the date, or some such, but since the date is almost
always today's date I would rather automate its entry.

I could use If Me.NewRecord to add the date, but if the user navigates to
another record without adding subform records there will still be a parent
record. I would probably have to run a Delete query of some sort to get rid
of the childless parent record.
 
K

KARL DEWEY

Is it possible to save a record that has no user-input values, but only
Default Values?
Based on the information you supplied I say no unless you are using an
autonumber for primary key.
 
J

John W. Vinson

Default Values?
Based on the information you supplied I say no unless you are using an
autonumber for primary key.

Not even then, actually, since there's nothing to "dirty" the record.
 
J

John W. Vinson

I could use If Me.NewRecord to add the date, but if the user navigates to
another record without adding subform records there will still be a parent
record. I would probably have to run a Delete query of some sort to get rid
of the childless parent record.

That's sort of a "chicken or egg" problem: you can't have a child record
unless there is a parent record, and (by your rule) you can't have a parent
record unless there is a child record! At *some* point in the process one
record (the parent) must be created first.

It sounds like your parent table in the one-to-many relationship contains
nothing but a date. Is that the case? If so, what purpose does this table
serve? What Entities (real-life people, things, or events) are modeled by your
tables, and how are they related? Perhaps this table isn't needed at all; if
you just want to automatically enter today's date in what's now the child
table, could you instead simply use a date/time field defaulting to =Date() ?
 
B

BruceM via AccessMonster.com

I had the details in the first posting, but simplified for this thread
because I had not received a reply. The situation is that documents are sent
around for review. Typically there are several at once (a batch, for lack of
a better term). The parent record contains fields for StartDate, EndDate,
and a Yes/No field that is a bit difficult to describe. In a sense each
document is being individually reviewed, but in practice related documents
are reviewed together. The Batch record does little more than provide a way
to group the records. I cannot use the StartDate as the grouping field, as
several batches may be sent to different sets of people on the same day.

The general structure is as follows:

tblBatch
BatchID (PK)
StartDate
EndDate
YesNoField

tblDocument
DocID (PK)
Doc_BatchID (linking field)
DocNumber
Description
etc.

tblReview
ReviewerID (PK)
R_BatchID (linking field)
EmployeeID (from Employee table)
ReviewDate

tblIndividualReview
IR_ID (PK)
IR_DocID (linking field)
IR_EmployeeID
IR_Date

tblEmployee
EmployeeID (PK)
FirstName
etc.

There may be ten documents in a batch, all of which are reviewed by the
employees listed in tblReview, but one of which is to be reviewed by another
employee. That is why there is tblIndividualReview. I could have handled
the two separate Review tables differently, perhaps by using Append SQL to
add the all-document reviewers at once to all of the IndividualReview records
(rather than having to fill in each IndividualReview record separately), but
that still leaves me with the Batch record and the DefaultValue problem.

I may have to require that the date be entered in tblBatch. There is another
possible option I can think of. The Yes/No field is Allen Browne's Yes/No
replacement field, which is a number field with a Default Value of 0. I can
omit the Default Value, and instead set the field to 0 in the Enter event of
the subform control for the subform bound to tblDocument, then save the
record. That seems to work, but I am afraid there may be difficulties I have
not yet discovered. In the end the best thing may be just to hide the
subform controls for a new record until the user enters a StartDate. Or
maybe I have overlooked something about how to structure the whole project.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top