macro to create primary key value

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

Guest

I have an append query that takes a value from a control on a form, to then
calculate what will end up being the primary key value for that record (a
student number). I then try to run a macro to automatically pull that new
student number into the control on the form, and no matter what I try, I end
up running into errors related to the record trying to be saved on the form
without a primary key. When the form is closed and reopened, the new record
is there, but I am trying to find a way to avoid the error messages.

Any help would be greatly appreciated...

Thanks
 
Smith,

What control on what form? What is the value? What is the calculation?
What actions are in your macro? What is the error? Thanks.
 
Hi Steve,

Thanks for wading in. I posted that note late last night, and was not
thinking properly. I have two fields, one "Start Year" and the other
"Student Number". I want on this form for the student's start year to be
selected from a combo box, and for the append query to "calculate" the
student number, based on the start year and a DMax() + 1 function on another
field called Index Number. The Student Number itself is of a format xxyyyyy,
where xx are the two digits representing the start year, and yyyyy represents
the incremented index number. That query then populates a master student
table with the start year, index number and student number values. The query
works fine, and appends the data to the table. BTW, the Student Number field
is the primary key for this Student table (the data entry form itself is
based on a query that provides a subset of the entire student list, because
it is focussed on future year students only).

The macro, after executing the append query, then turns Echo Off, sets the
value of the Student Number control on the data form to Student Number, and
tries to close the form, and reopen it, going to the last record, before
turning Echo back on. When trying to close the form, I get the error message
"Index or Primary field cannot contain a null value" because the control on
the data entry form itself never gets the calculated Student Number value.
Once I the clear the error message, the macro ends, and the new record is in
fact there, complete with new Student Number.

I am self-taught in Access, and would like to know what I am doing wrong,
and if there is a better way of achieving this...?

I hope this provides you with more details.

Thanks
 
Smith,

I can't grasp what you mean by "The macro ... sets the value of the
Student Number control on the data form to Student Number"... where does
it get that from? But anyway, it seems to me that an Append Query is
not the appropriate method to use here. You are effectively starting a
new record on the form, when you enter the Start Year, and then not
finishing that record, but adding *another* new record to the table
based on this Start Year entry. If you want to use an apopend query,
maybe it would be more suitable to enter a start year value into an
*unbound* textbox or combobox, so you haven't triggered the opening of a
new record, and then the append query will be what makes the new record
at that point, including the incremented student number. Another way to
do it, I suppose, is to forget the append query idea, leave the Student
Year bound, and just use a SetValue action in your macro to write
directly to the Student Number control on the form, maybe on the After
Update event of the Student Year combobox.
 
Steve,

Thanks for your help. Your suggestion was right on. I was in effect
creating two new records for each run of the macro. I am now using an
unbound dialog form to now enter the start year, which is then passed on to
the Append Query.

Many thanks for your feedback and comments....
 
Back
Top