Automatic population of field in adding a new record

[mor]

Hello everyone,

I would like some help in determining how to automatically populate a certain field. To add some context, I have a form which registers the details of a contact with standard information of contact details. There is a subform which shows the different products that the client from the main form is interested in. This is a actually a data sheet which returns the results of a query (selecting from the relevant table the client in question and the products he/she wants).

I have added a button which opens up another form and allows a product (and hence a new record) to be added for that particular client. I would like that the form automatically populates one of the fields in the form that is the client id. Given that the subform is opened from a form which already identifies the client, how do I do this?

Many thanks,
MOR

 

[JHB]

You can use the form open arguments:

  DoCmd.OpenForm "YourFormName", , , , , , Me.[YourClientIdControlName]

And then on the form's Open Event you open:

Private Sub Form_Open(Cancel As Integer)
  Me.[YourClientIdControlName] = Me.OpenArgs
End Sub

 

[Mihail]

What if the client change his mind and do not wish to fill other fields ?
So a better approach is:

Private Sub Form_Open(Cancel As Integer)
  Me.YourClientIdControlName.DefaultValue = Me.OpenArgs
End Sub

Better is to use directly the ID from the main form:

Private Sub Form_Open(Cancel As Integer)
  Me.YourClientIdControlName.DefaultValue = Forms!TheMainFormName!TheIDsControlName
End Sub

So, you open the second form with this code:

If IsNull(Me.TheIDsControlName) Then Exit Sub
DoCmd.OpenForm "YourFormName"

 

[mor]

JHB,

Thank you for this, but I have a quick question. When I try to run the code in the On Open event, I get the error "You can't assign a value to this object".

However, if I run the code from another event, eg OnClick event for the control in question, then the code works with no problems.

I actually ran the event in On Load instead of On Open and this resolved the problem. Would you have any idea why this happens?

Many thanks,

MOR

 

[JHB]

Better is to use directly the ID from the main form:

Private Sub Form_Open(Cancel As Integer)
  Me.YourClientIdControlName.DefaultValue = Forms!TheMainFormName!TheIDsControlName
End Sub

No it isn't better, it is the worst solution. You lock your self to use a certain form and control - if you rename one of them, the code wouldn't work. And you can't use the form from another form if you want to.

 

[JHB]

...
I actually ran the event in On Load instead of On Open and this resolved the problem. Would you have any idea why this happens?
..

Good you search another event to use, instead of yelling. Thumbs up from me!
I think MS-Access isn't ready to store the value in the object, at form's open time.

 

[Mihail]

You lock your self to use a certain form and control - if you rename one of them, the code wouldn't work. And you can't use the form from another form if you want to.

Indeed, JHB.

(For me is an acceptable cost)

 

[nanscombe]

Personally, I've always used the openForm with openArgs method. The only difference is using the Form_BeforeInsert() event to apply the ID as a record is inserted.

Calling form

DoCmd.OpenForm "YourFormName", , , , , , Me.[YourClientIdControlName]

Receiving form

Option Explicit
Dim myParentId as variant

Private Sub Form_Open(Cancel as Integer)
  myParentId = Me.OpenArgs
End Sub

Private Sub Form_BeforeInsert()
  Me.yourParentIdField = myParentId
End Sub

That will populate the parent Id as the user starts to add data to the record.

Either that or I would use the Form_BeforeUpdate() event to add it as the record is saved.

Private Sub Form_BeforeUpdate(Cancel as Integer)
  If Len(Me.yourParentIdField & vbnullString) = 0 Then
    Me.yourParentIdField = myParentId
  End If
End Sub

 

[Mihail]

Hi Nigel !
Are you so kind to explain where (in what situations) the BeforeInsert should be used ?

Thank you !

 

[nanscombe]

When using the BeforeUpdate() event the field would only be populated as the record was being saved so would not be available until that happened.

By using the BeforeInsert() (or possibly the AfterInsert() ) event the field would be populated, and therefore available, as soon as the user starts typing in a new record.

 

[Mihail]

OK. I understand this. Seems to be equal (the same, equivalent) with the Default value for a control (that is bound to a field) or even to the Default value that is declared, at table level, for a field.
But, what I ask you, is about the (very) special situations when this event should be used.

 

[nanscombe]

Ok. I was a bit too early with Form_BeforeInsert() as this fires, as the name says, before the record is even created. This can be used to check whether a user really wants to create a new record and cancel if it was a mistake. I guess you could even prompt for a password like when you open a form.

It looks like the AfterInsert() event is where any fields could be automatically filled in, or dependencies checked.

Why don't I use the DefaultValue property? If I have fields like Id, parentId or dateStamp (when the field was updated) I don't create a control for them, I write the value directly to the underlying field using "Me!fieldName" rather than "Me.ControlName".

If there is no control there is no DefaultValue for it.

I would probably update parentId in the AfterUpdate() event because the value only needs to entered once and will not change.

I would update Id in the BeforeUpdate() event because there would allow minimal time to pass where I could accidentally create a duplicate.

I would use the BeforeUpdate() event to update dateStamp because that is when the change is made.

 

[Mihail]

Thank you.
Even if I don't fully understand, now I have a start point for my own research.

 

[nanscombe]

These pages might be useful:
Form.BeforeInsert Event
Form.AfterInsert Event

BeforeInsert → BeforeUpdate → AfterUpdate → AfterInsert.

Oh dear, looks like I may be talking rubbish with the Insert() events (or am I?), just stick to the BeforeUpdate() event which is what I used anyway.

 

[Mihail]

Thanks again. I've bookmarked the links.

 

[nanscombe]

... Oh dear, looks like I may be talking rubbish with the Insert() events (or am I?) ...

No. I just tried it and the Form_BeforeInsert() event does allow me to pre-populate fields on a form.

 

[gemma-the-husky]

before insert:

access is ready to insert a new record. you are setting some of the fields. you would get a similar effect from default values at table level

you could also do this with afterinsert, or beforeupdate.

now the actual event you choose depends on exactly what the fields are, I would think. If they are "key" values that would affect other users, (such as a sequential ID) then you need to be careful, in case another user gets the same value. if it is just data, it doesn't matter too much.

 

[Pat Hartman]

I use the BeforeInsert event because it doesn't fire until the user starts typing. Once the user starts typing, he knows that he has dirtied the record and will not be surprised by any error messages or save prompts. If you populate the foreign key in the Load event or Current event (the only two other reasonable options), you will be dirting the record and if the user doesn't complete the data entry or do any at all, he will be confused when he tries to leave the form without creating a new record. You also will have to worry about creating empty records.

You could use the BeforeUpdate event but you will need to check first to determine if this is a new record.

If Me.NewRecord Then
Me.FK = Forms!yourform!PK
End If

The AfterInsert event runs after the record is saved so is not appropriate for this purpose since the record would be saved without the FK which would generate an error anyway if you have properly defined referential integrity.

 

[Mihail]

Thank you very much for all explanations.

Why don't I use the DefaultValue property? If I have fields like Id, parentId or dateStamp (when the field was updated) I don't create a control for them, I write the value directly to the underlying field using "Me!fieldName" rather than "Me.ControlName".

If there is no control there is no DefaultValue for it.

I'll believe that I'll change my old technique.
Until now I always have a control bound to such a field. Not visible and moved on the form Header or Footer section.
On the other hand my old approach allow me to easy debug the form by turning on the visibilities for this controls

.
OK, I'll see

Thank you again !

@mor
Forgive me, please, because I've posted a question in your thread, but at that time I haven't think that will be the start point for a tsunami of information.
Hope that was a help for you too.