Automatically add a form value to its underlying table

[amjjam]

A registration form is based on an underlying registration table. The form
contains a RegistrationID auto number box. I have based a confirmation number
on the RegistrationID auto number in the form. The confirmation number's
control source is: =[RegistrationID]+351. The confirmation number shown in
the form is correct. (It starts at 352.) It fails to transmit to the
registration table correctly. The table shows a default value of zero in the
confirmation number field. How can I get the form data to transmit correctly
to the table? I'm a complete novice in Access, so any help is appreciated,
but please make the steps really clear. Thanks, amjjam

 

[Douglas J. Steele]

Because you've set the ControlSource to a function call, you've made the
field unbound.

You either need to bind the field (by setting its ControlSource to the field
name) and assign the value to the control in the form's Current event:

Private Sub Form_Current()

If Me.NewRecord Then
Me!ConfirmationNumber = RegistrationID + 351
End If

End Sub

or else you need to set the value of the field in the form's BeforeUpdate
event:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Me!ConfirmationNumber = RegistrationID + 351

End Sub

 

[amjjam]

Thanks, but I can't get either method to work. The confirmation number
doesn't show up in the form view or the table when I bind the control source
to ConfirmationNumber and insert:

Private Sub Form_Current()

If Me.NewRecord Then
Me!ConfirmationNumber = RegistrationID + 351
End If

End Sub

When I leave the Confirmation Number's control source as
=[RegistrationID]+351, and insert the following in the forms Before Update
event:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Me!ConfirmationNumber = RegistrationID + 351

End Sub

the correct confirmation number shows up in the form, but still fails to
appear in the table.

I have only rudimentary knowlege of Microsoft Visual Basic, so I may be
inserting the code incorrectly or in the wrong spot. I just really need for
the confirmation number and the registration id number to be different, and I
don't want to insert the confirmation numbers in the table by hand.

Because you've set the ControlSource to a function call, you've made the
field unbound.

You either need to bind the field (by setting its ControlSource to the field
name) and assign the value to the control in the form's Current event:

Private Sub Form_Current()

If Me.NewRecord Then
Me!ConfirmationNumber = RegistrationID + 351
End If

End Sub

or else you need to set the value of the field in the form's BeforeUpdate
event:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Me!ConfirmationNumber = RegistrationID + 351

End Sub

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

A registration form is based on an underlying registration table. The form
contains a RegistrationID auto number box. I have based a confirmation
number
on the RegistrationID auto number in the form. The confirmation number's
control source is: =[RegistrationID]+351. The confirmation number shown in
the form is correct. (It starts at 352.) It fails to transmit to the
registration table correctly. The table shows a default value of zero in
the
confirmation number field. How can I get the form data to transmit
correctly
to the table? I'm a complete novice in Access, so any help is appreciated,
but please make the steps really clear. Thanks, amjjam

 

[Jack Cannon]

I am guessing that the failure you are encountering
could be due to the fact that [RegistrationID] has
not been established at the time that you are
attempting to load it.

RegistrationID will be null until something
causes the form to be Dirty.

Try using the Form_Unload event to load the
confirmation number with something like:

If Not IsNull(Me![RegistrationID]) then
Me![ConfirmationNumber] = Me![RegistrationID] + 351
End If

Jack Cannon

 

[Ken Sheridan]

If the confirmation number is always the registration ID + 351 then
you don't need to store it in the table at all. Moreover you should
not do so as it introduces redundancy and the possibility of
inconsistent data being entered in that someone can change the
confirmation number so that it doesn't have this fixed relationship to
the ID number.

Whenever you need the confirmation number you can compute it in the
same way as you have on your form, e.g. in a form or report by means
of an unbound text box with a ControlSource property of =
[RegistrationID]+351. In a query you can add a computed column by
putting the following in the 'field' row of a blank column in query
design view:

ConfirmationNumber: [RegistrationID]+351

Ken Sheridan
Stafford, England

 

[amjjam]

Thanks. Leaving the confirmation number out of the table and using it in
forms and reports is the most useful solution for my needs. I appreciate the
help.

If the confirmation number is always the registration ID + 351 then
you don't need to store it in the table at all. Moreover you should
not do so as it introduces redundancy and the possibility of
inconsistent data being entered in that someone can change the
confirmation number so that it doesn't have this fixed relationship to
the ID number.

Whenever you need the confirmation number you can compute it in the
same way as you have on your form, e.g. in a form or report by means
of an unbound text box with a ControlSource property of =
[RegistrationID]+351. In a query you can add a computed column by
putting the following in the 'field' row of a blank column in query
design view:

ConfirmationNumber: [RegistrationID]+351

Ken Sheridan
Stafford, England

A registration form is based on an underlying registration table. The form
contains a RegistrationID auto number box. I have based a confirmation number
on the RegistrationID auto number in the form. The confirmation number's
control source is: =[RegistrationID]+351. The confirmation number shown in
the form is correct. (It starts at 352.) It fails to transmit to the
registration table correctly. The table shows a default value of zero in the
confirmation number field. How can I get the form data to transmit correctly
to the table? I'm a complete novice in Access, so any help is appreciated,
but please make the steps really clear. Thanks, amjjam