Find Max number

[NISMOJim]

This should be an easy one, but I'm just not getting it...

I have a table, tblDel that stores information about deliveries (Product, Amount, Price, PO Number, etc.).
Another table, tblPO stores information about the purchase orders (PO Number, PONumberIndx [autonumber] Amount, Purpose [regular or special], etc.).
Each year, 2 PO Numbers are given to me, one for Regular purchases, one for Special purchases.


These 2 tables are joined in a query by PO Number, and my form is based on this query.

Since a user won't remember a PO Number (which is a text field property because it is 10 digits long), I want them to be able to select from a combo box whether it is a regular or special PO. Once they select, the PO Number should show up in txtPONumber. It should be the PO Number with the highest PONumberIndx where the POPurpose = the cboPOPurpose that the user selected. I'm thinking something like this, but it isn't right...

cboPOPurpose AfterUpdate
Me.txtPONumber = DMax("[PONumberIndx]", "qryDelivery", "Purpose = Me.cboPOPurpose")

Any help into what I'm doing wrong is appreciated, and I won't be offended if you point & laugh!

[ssanfu]

The syntax is a little off. You have to concatenate the combo box value; because it is text, you also need to use the correct delimiters.

Me.txtPONumber = DMax("[PONumberIndx]", "qryDelivery", "[Purpose] = '" & Me.cboPOPurpose & "'")

Text strings must be delimited with quotes. Expanded, the last part (in red) is: " ' "

I probably wouldn't use DMax() to get this year's PO number - it would depend on what the PO number look like.
What do the PO numbers look like? Is there a special sequence of letters and numbers?


Do you also have a year field in the purchase orders table? You could then do something like

Me.txtPONumber = DMax("[PONumberIndx]", "qryDelivery", "[Purpose] = '" & Me.cboPOPurpose & "' AND [POYear] = " & Year(Date))

[NISMOJim]

I think I messed us both up. This is working good to show the PONumberIndx in the textbox (just like I asked), but what I want it to show is the PO Number itself that would be part of the same record as the PONumberIndx.

The PO Number is just a 10 digit number, no letters or decimals. That's why I had to use it as a text property. Unless I'm mistaken, Long Integer only holds up to about 6 digits as a whole number. The PO Numbers are issued to all departments sequentially, but the numbers I get may be hundreds apart. The PONumberIndx is just an autonumber in the table. That will be sequential for each number I get through the years.

Ultimately, I need the PONumber to show in the textbox, derived from the DMax PONumberIndx with the POPurpose clause like you showed me in your post. Could that be nested in there somehow?

Thank you for your help.

[NISMOJim]

I just had another thought that might be even easier...

Since the tblPO has all of the information about the PO, maybe the combobox on the form should have the Control Source of PONumber from the query, but the Row Source could be from tblPO. It could show the column for the POPurpose (Regular or Special) but have the Bound Column for the PONumber.

Does this sound like a simpler and more logical way to go? Since I came up with it, I think it best to question my own ideas.

[ssanfu]

Does this sound like a simpler and more logical way to go?

Yes, sounds good.
See attached dB...... something like this?

[NISMOJim]

Yup, this part is working good, but (of course) now I've run into another problem...

I have a tab on the form that shows the amount of the PO, all purchase costs on each PO, the contact information, etc. for the user to reference when placing an order. These numbers had a Control Source of the fields in the qryDelivery, coming from ttblPO. When I entered information on the form and saved it, it saved all of the PO information as a new record in the PO table, creating duplicate records. To counter this, I removed the join properties between the tblDeliveries and the tblPO on the query, and changed all textboxes with this info to Unbound. Now this too is working good. However I would still like to show this information on the form. I tried adding the rest of the table fields to the new qryPO that I am now using as the Row Source for the Purpose combobox we got working before, but then it shows all of the PO Numbers again instead of the Max numbers. Here is what I'm trying to add to the After Update property on the cbxPOPurpose box above for the first bit of information to show, but I'm getting a Compile Error on the first comma after [POAmt]...

Me.txtPOAmt = ("[POAmt]", "tblPO", "[PONumber] = '" & Me.txtPONumber & "'")

If we can figure this part out, I have one more potential problem coming up, but for now, one step at a time.

Thanks for your help.

[ssanfu]

I tried adding the rest of the table fields to the new qryPO that I am now using as the Row Source for the Purpose combobox

I would use that query for the combo box only.
There is nothing wrong with creating another query with the additional fields.

Would you post the dB with a few records? (change any sensitive data)

If you are trying to get the total PO amount and put it in "txtPOAmt", one way is to write a VBA function.

[NISMOJim]

OK, I got it. The textboxes I had on the form that showed the different PO Information...I changed them all to listboxes so I could control the Row Source, then used the query builder there to pick & choose the data I wanted to see. I then requery each listbox when I change the POPurpose, and it updates them all.

Thanks for leading me in the right direction. I still have a couple things to try out, so I'll get back soon if I need more guidance.

Thanks again!

[ssanfu]

Sounds complicated, but if it works for you.......

[NISMOJim]

This is one of the first databases I built about 5 or 6 years ago. Kinda embarrassing looking at how I did things back then when I knew nothing. Not that I'm much more educated now, but it isn't easy going back and fixing things that were put together with so many moving parts. Not sure what I was thinking back then. As complicated and strung out as it is, it does do the job quite well though.

[ssanfu]

Yep, I look back at early dBs and wonder "What was I thinking???".

It is hard to restructure, but there are many good tools to help. Good luck

[NISMOJim]

One last question on this if you don't mind, and I think I may be done with the latest project update. As are most of my posts, it gets complicated, so I will try to explain in detail.

I have a textbox (ChgAmt) on my form (FlDel) that the user types a delivery amount into (formatted as currency).
An unbound listbox (D1) shows the cost of the first delivery of the year (also formatted as currency).
Other unbound listboxes (D2, D3, etc.) are set up the same way for each consecutive delivery of the year.
I used the query builder as the Row Source for each of the listboxes as follows, and the Bound Column is 2...
SELECT qryPOInfo.PONo, qryPOInfo.D1 FROM qryPOInfo WHERE (((qryPOInfo.PONo)=[Forms]![FlDel]![PONo]));
The Query qryPOInfo is based on the table PO. In this table, the fields D1, D2 etc. are all formatted as currency, and have a default value of 0.
(I hope I haven't lost you yet!)
I've added a command button (cmdUpdate) to the form, and I would like it to do the following, but I'm having a hard time coming up with the context...

The user should enter an amount in the ChgAmt textbox, then click the cmdUpdate button. If D1 (either the unbound listbox, or the D1 field in the table, I'm not sure if it matters) = $0.00 then insert into the PO table in the D1 field, the amount showing in the ChgAmt textbox, but the PONo field in the table must match the PONo textbox on the form.
I've tried this on the On Click event of the command button, but nothing is happening...
If Me.D1 = "$0.00" Then
CurrentDb.Execute "INSERT INTO PO (D1) VALUES (" & Me.ChgAmt & ") WHERE [PONo] = " & Me.PONo
Me.D1.Requery
I understand this is all way more complicated than it probably needs to be, but as I said before, I sure don't want to start this thing all over when it has been working so good. Just trying to make a few more parts of it easier to use.

Thank you again!