Passing Form Field Value to Query

[calgarianguy]

Good Day Everyone,

Just wondering if I can get some help you one of the Access Guru for my Access problem.

I am trying to group by my query based on supplier's name and retrive the last serial number of the backcharge and add 1 into that for the next one.

Record would be sort like below:

Servial # Supplier Name
1 AB
2 AB
3 AB
1 CD
2 CD
1 EF
2 EF
3 EF
4 EF

On the form if Supplier AB is focus in down down box I want to get 4 in next serial number field?

Please advise how to achieve above result.

-------------
Following are some of my thought that I tried and did not work

Query:
SELECT Count(FinalBackchargeLog.FSupToBeChargedNumber) AS MaxOfFSupToBeChargedNumber
FROM FinalBackchargeLog
GROUP BY FinalBackchargeLog.FSupplierToBeCharged


HAVING (((FinalBackchargeLog.FSupplierToBeCharged)=Forms![Add New Backcharge]!txtFSupplierToBeCharged));

On Event:
Dim loc As String
Me.txtSerialNo.SetFocus
loc = DLookup("MaxOfFSupToBeChargedNumber", "BackchargeLogSerialNumber_Q")
Me.txtSerialNo.Value = loc + 1

[June7]

What is purpose of query? Do you want to build a report? What is purpose of the serial number?

Textbox in report has a RunningSum property that will allow dynamic generation of the serial number. It won't be saved in table but the record on report will display the value.

Otherwise, saving calculated data (data dependent on other data) can be tricky, as you are discovering. Why doesn't it work - error message, wrong results, nothing happens?

Probably don't need the query. Try DMax. Assuming FSupplierToBeCharged is number type.

If IsNull(Me!loc) Then Me!loc = Nz(DMax("FSupToBeChargedNumber", "FinalBackchargeLog", "FSupplierToBeCharged)=" & Me.txtFSupplierToBeCharged),0) + 1