How to update fields on a form using previous inputs (1 Viewer)

[bftsg]

Hi All - can you help me or point me in the right direction please?
I have written a form that uses three drop-down boxes to enable a user to select three different areas and then generate a new number value in the fourth field.
The dropdown boxes work fine and the system does fine the next highest number for the fourth field but how can I populate the field? I have tried using After Update on the third field but that doesn't work.

Any ideas please?

 

[pbaldy]

The after update event of the third combo sounds like the right place. What exactly was your code? What exactly does "doesn't work" mean (error, incorrect result, etc).

 

[Ranman256]

You can just assign the value to the field...
TxtFld4= txtFld1+txtFld2* txtFld3

 

[bftsg]

The after update event of the third combo sounds like the right place. What exactly was your code? What exactly does "doesn't work" mean (error, incorrect result, etc).

The previous three fields display ascii values but the key to the are numeric. I use the combination of the three values to identify the current highest value plus 1 for the fourth key.

I am using
DoCmd.OpenQuery "tbl_requirement_testings_Query"
Forms!frm_Requirements_Create!Req_No = nextone + 1
Me.Refresh

The query gets the max value of the fourth field as 'nextone'.

All it does is display the value on the screen but doesn't update the fourth field. Obvioulsy I have got it wrong in the way I am doing it.

Thanks for your help so far

 

[pbaldy]

You'd have to use a DLookup() or a recordset to get the value from the query. You can't just refer to a field in it like that.

Forms!frm_Requirements_Create!Req_No = DLookup("nextone", "tbl_requirement_testings_Query") + 1

 

[bftsg]

You'd have to use a DLookup() or a recordset to get the value from the query. You can't just refer to a field in it like that.

Forms!frm_Requirements_Create!Req_No = DLookup("nextone", "tbl_requirement_testings_Query") + 1

You are a genius! Worked like a charm.

Many thanks. Something else I have learned today

 

[pbaldy]

Happy to help!

 

[pbaldy]

By the way, you may want to add something to account for the possibility that the query doesn't return a record (Nz function or whatever).

 

[bftsg]

By the way, you may want to add something to account for the possibility that the query doesn't return a record (Nz function or whatever).

Thanks - how could I do that? I did try if isnull but that doesn't work. Sorry to be a pain.

 

[pbaldy]

Well, I mentioned the Nz function:

Nz(DLookup("nextone", "tbl_requirement_testings_Query"), 0) + 1

 

[bftsg]

Well, I mentioned the Nz function:

Nz(DLookup("nextone", "tbl_requirement_testings_Query"), 0) + 1

Sorry but I cannot get it to work. I have tried lots of different ways but it doesn't work for me. I have put it as two steps in my proc but it simply seems to ignore the nz command and returns a null value anyway.

Forms!frm_requirements_create!Req_No = DLookup("nextone", "tbl_requirement_testings_Query") + 1
If Nz(DLookup("nextone", "tbl_requirement_testings_Query"), 0) = 1 Then
nextone = 1
End If :banghead:

 

[pbaldy]

The If test shouldn't be necessary, the Nz() should be in the line above as I posted it. Can you attach the db here?

 

[bftsg]

The If test shouldn't be necessary, the Nz() should be in the line above as I posted it. Can you attach the db here?

I have managed to do it using the nz option after the fields were populated. If the rec_id is null it sets it to one but doesn't change it if it isn't null.

Forms!frm_requirements_create!Req_No = DLookup("nextone", "tbl_requirement_testings_Query") + 1
'****************************************
Dim frm As Form
Dim ctl As Control
Dim varResult As Variant
Set frm = Forms!frm_requirements_create
Set ctl = frm!Req_No
varResult = IIf(Nz(ctl.Value) = vbNullString, _
"1", ctl.Value)
'****************************************
Forms!frm_requirements_create!Req_No = varResult

End Sub

I really do appreciate your help and guidance.
Thanks you

 

[pbaldy]

You're saying this doesn't work by itself?

Forms!frm_requirements_create!Req_No = Nz(DLookup("nextone", "tbl_requirement_testings_Query"), 0) + 1

 

[bftsg]

You're saying this doesn't work by itself?

Forms!frm_requirements_create!Req_No = Nz(DLookup("nextone", "tbl_requirement_testings_Query"), 0) + 1

Just tried it again and it returns a zero for new records instead of it being set to 1.

Not sure why but at least I have a resolution thanks to your guidance.

Cheers