Use automated field to update table entry.

[wdrspens]

Hi,
I have an ID field in my form in which, at present, I manually enter the "ID" number (duplicates allowed). I have another field which shows me the last entered ID number and I add 1 to give the new number to be entered manually. I would like to automate the process, with the ability to override the automated result. (Autonumber cannot be used because duplicates are allowed.)
I can get the form to show the new number in the field by using the DMax formulae as the record source and adding 1, but when I move to the next field (or record), the new number does not show in the table on which the form is based, although any entries in the form made manually do so show.
How can I compel the table to show the automatic entry?
I have tried using the the DMax formulae in the "Default Value" but that doesn't work, probably because the DMax formulae cannot take effect until another field on the form has been entered from a combo box. I don't think an update query will work since it is only the new ID that needs to be updated and chaos would result if all IDs were automatically updated.
Any help would be greatly appreciated.
David

 

[wazz]

this is a bit confusing. not sure if it's just terminology or a design problem.

...I have another field which shows me the last entered ID number...

do you mean field (in a table), or control (on a form)?

...when I move to the next field (or record), the new number does not show in the table on which the form is based, although any entries in the form made manually do so show.

a table's record/row doesn't update when you move between fields, only when you move to a new record or save the current record.

I have tried using the the DMax formulae in the "Default Value" but that doesn't work, probably because the DMax formulae cannot take effect until another field on the form has been entered from a combo box.

see above.

you could update the ID with a DMax function and even change it on the fly, but you would have to then check that the ID is not already in use. hard to tell what's happening here.

 

[Rabbie]

Bearing in mind the excellent comments from wazz it would be helpful if you could post a copy of your DB so we can see exactly how far you have come

 

[wdrspens]

Sorry if I am not clear.

I am using a form to enter New Data into my database (it is much too large to provide a copy) The ID field for one table on which the form is based consists of 6 digits, for example 124007 or 119114, the first three of which are used to identify the customer and the last three of which are consecutive for that customer. I use the DMax function to find what is the highest consecutive number for that customer already entered into the underlying table and increase it by 1 and manually enter the result into the ID field.
I would like to automate that task with the possibility of an over-ride to the automated task. If I use the DMax function and add 1 as the control for that field on the form, the underlying table is not updated when I move to the next record, that field showing NULL. If I enter the number manually the underlying table is updated when I move to the next record.
What I want to do is force the field to be updated with the automatic number.

In answer to your questions:

1. I mean a field in a table underlying the control on the form.

2. I haven't tried saving the current form data - will give that a try, but it does not update when I move to another record.

3. I do not need to check whether the ID is already in use since this is a single user database - makes life easier

Thanks

David

 

[wdrspens]

I have now tried saving, but the underlying field is not updated.

The code I put into the control is =DMax("[BookingFormNo]","DetailNewQuery","[Organisation] = '" & [Organisation] & "'")+1

Thanks

David

 

[wazz]

if a field is not being updated it sounds like an unbound field, not tied to a table.

if the expression you listed above is actually on the ID control (bound to the table), every record you go to will be updated (+1) as soon as you land on it; you might not even notice the changes.

you say you get the info from your expression and then manually enter it. where is the initial info sent to; which control? and then, into which control do you manually enter it; a different control? still a bit confused as to which control does what. are you trying to add the DMax data (+1) to new records only?

 

[wdrspens]

Yes the form is only used for new records, and cannot be used for existing records.

I have on the form a text box (not in the underlying table) called "LastNo" into the record source of which I enter the DMax code given above but without the +1. That produces the Last Number entered for that particular organisation.

I then type in that number plus 1 for the new ID and I have no problems.

I suppose what I really want to do is learn how to bind the field. I have a bound control for the field "OrgNoID" whose record source is shown as "OrgNoID". I change that record source to "=[LastNo]+1" and the resulting correct number appears in the correct field in the table, but, and here is the problem, it does not update the field in the table. It is therefore a question of how do I bind it to the field in the table?

I am very sorry if I haven't made myself clear, but hope that the problem is now more understandable.

Thanks

David

 

[Banana]

The thing is that a control can't be both a calculcated control and a bound control at same time. It's either one or other.

One approach to this would be to bind the field to the ID column, set its Enabled property to false as we don't need users to mess with it, then on form's AfterUpdate event, insert the new ID value to the table. The disabled control should then display the new ID.

Be aware this requires delicate handling as this could cause loops or write conflicts if we do not process the event correctly.

 

[wdrspens]

Thanks,
I think you have answered my problem, in that if I disable the control I cannot override the calculation, and I cannot bind the control and have it as a calculated control.
I will continue to use the manual insertion method.
Thank you all very much
David

 

[Banana]

Just to clarify:

if I disable the control I cannot override the calculation

While that is true, that wasn't my point. My point is that you still can do calculation upon bound controls by using VBA to do the calculation and placing the result of calculation inside that bound control.

I cannot bind the control and have it as a calculated control.

Correct.