Update on Inserting New record

[mike60smart]

Hi Everyone

I have an Unbound Main Form with a Combobox which allows the user to select a record from a list of Imported data.

On The After Update of the Combobox it runs an Append Query to append the selected Record to the Required Table.
It also requeries a Continuous Form bound to the table.

What I want to happen is when the Continuous Form is requeried I want the Control "LoadNumber" to increment by 1

Where would I place the following Code in the Subform?

Me.LoadNumber = Nz(DMax("LoadNumber","Loads"),0) + 1

Any help appreciated

 

[bastanu]

If the LoadNumber control is bound to the LoadNumber field in Loads you don't need it; you do that in your append query (add a calculated field with that expression to load it into the empty LoadNumber field for the new record). If the control is unbound in a continuous form it will be the same for all records which is probably not what you want.

Cheers,

 

[mike60smart]

Hi Vlad
The field is not part of the Import and therefore not in the table being used for the Append.

 

[bastanu]

Then you either need to add it or use a ranking query to get your numbering; if the control is unbound it will display the same value for all the records in a continuous form.
EDIT: is the continuous form based on the import table or the target table - the target should be the one where the field is located.
Cheers,

 

[mike60smart]

Hi Vlad

No the process is as follows:-

1. User clicks Command Button which Imports data to tblActiveLoads
2. On an Unbound Form the is a Combobox whose Row Source is :-
SELECT AvailLoadsLkd.[Load ID], AvailLoadsLkd.AvLdID, AvailLoadsLkd.[Agent ID], etc......
3. On the After update of the Combobox the record selected is appended to tblLoads which the Continuous Form is Bound to.
(The tblLoads has the LoadNumber field)
4. When new records are added to the Subform the Load Number Increments.

Hope this is a bit clearer?

 

[bastanu]

Does #4 already happen or is that your question, how to make it increment? If already happening I don't understand your original question; if it doesn't then my answer in post # 2 applies. To the append query that runs in point # 3 add a calculated field LoadNumber:Nz(DMax("LoadNumber","Loads"),0) + 1 and append it to the LoadNumber field in tblLoads; bind that field to the textbox.

Cheers,

 

[mike60smart]

Hi Vlad

That works just fine on a Continuous form.
When I try switching it to a single form it just repeats the last LoadNumber?

 

[Pat Hartman]

@mike60smart #2 is the correct answer. Calculated fields can be appended to a table.

 

[Gasman]

Hi Vlad

That works just fine on a Continuous form.
When I try switching it to a single form it just repeats the last LoadNumber?

You keep moving the goalposts?

 

[bastanu]

Not sure what you mean Mike, how could it repeat the last # if it is bound to a fields holding a sequence (built by your append query in step # 3 in post #5)?
Cheers,

 

[ebs17]

When new records are added to the Subform the Load Number Increments.

You should also do this, not just say:

Private Sub Form_BeforeUpdate(Cancel As Integer)
   If Me.NewRecord Then _
         Me.LoadNumber = Nz(DMax("LoadNumber","Loads"), 0) + 1
End Sub

 

[mike60smart]

Hi Everyone

With the addition of ebs17's suggested solution it now works just fine.

Many thanks