Update Table from Form

[NISMOJim]

Hello Access Gurus,
I am experimenting with a form that the original builder never got working, and I can't figure it out either. Here's the set-up...

The Purchase Order table (tblPO) contains fields to record the cost of each delivery on a Purchase Order Number ([PONo], [POAmt], [D1], [D2], [D3], etc.).
The Delivery table (tblDel) contains details about each delivery ([DelDate], [PONo], [Cost], [Item], [Qty], etc.)
The Form (frmDel) is based on a query that contains the fields from tblDel. The user enters the date, the PO Number that the delivery is being charged to, and the cost of the delivery, etc.
Another tab on the form shows the fields from the query based on the PO Table (qryPO) in unbound textboxes (Row Source = SELECT qryPO.PONo, qryPO.D1 FROM qryPO WHERE (((qryPO.PONo)=[Forms]![frmDel]![PONo]));, and so on, for each [D#] field in the table). This way the user can see how much money has been charged on the PO. Another textbox calculates the remaining amount. This all works ok. Here comes the problem...

A command button on this tab is supposed to add the cost of the current delivery inputted on the form ([Cost]) to the next available field in tblPO, then refresh that textbox on the PO part of the form, and calculate the new dollar amount remaining on the PO ([PORmn]). Here is the code on 'On Click' of the Update button...

If Me.D1 = "$0.00" Then
CurrentDb.Execute "UPDATE tblPO SET [D1] = (" & Me.Cost & ") WHERE [PONo] = " & Me.PONo
Me.D1.Requery
ElseIf Me.D2 = "$0.00" Then
CurrentDb.Execute "UPDATE tblPO SET [D2] = (" & Me.Cost & ") WHERE [PONo] = " & Me.PONo
Me.D2.Requery
'And so on, through D15


End If


Me.PORmn.Requery

When the command button is clicked, nothing happens. The cost is not added to the table, so if the requery is happening, there is nothing to change. Any ideas? As always, thank you for the help.

[NISMOJim]

My mistake...the unbound boxes on the form ([D1], [D2], etc.) are listboxes, not textboxes.

[NISMOJim]

Does this not work because they are listboxes instead of textboxes? I would think you could update the table from the value in a listbox, but maybe I am mistaken. It seems that they need to be listboxes since they have a Row Source, which isn't available with a textbox.

[ssanfu]

I'm confused on your table structure. If table tblPO has fields D1, D2, ..., D15, then you do not have a normalized table structure.

You have field named "D1" and a list box named "D1"??? (more confusion )

Is the field type "tblPO.Dx" a text type or number type?

If Dx is a number, then "If Me.Dx = "$0.00" Then" is always false and will not update the field in the table.

"UPDATE tblPO SET [D1] = (" & Me.Cost & ") WHERE [PONo] = " & Me.PONo

Why are there parenthesis around Me.Cost??

[NISMOJim]

I simplified things a little bit for the post. You should see what the naming really looks like. I agree with the normalization, I would have done things a bit different myself. However, I said I would look at it & try to figure out why it isn't working.

The D1 field in the table is formatted as currency, with decimal places on Auto.
The unbound D1 listbox on the form doesn't have a format.

Not sure about the parentheses, but I tried it with & without them with no luck, thinking it might just be a syntax problem.

Overlooking the poor design, what is going wrong with not being able to update a table with a listbox on a form? I've also tried the value as "0", "$0" and "$0.00" but nothing is updating the table.

[NISMOJim]

I figured if I skip the form altogether and go straight to the table to look for the value of D1, I could eliminate the problem. Now I get a "Run time error 424 Object required". Here is the code I tried...

If Tables![tblPO]![D1] = "$0.00" Or IsNull(Tables![tblPO]![D1]) Then
CurrentDb.Execute "UPDATE tblPO SET [D1] = " & Me.ChgAmt & " WHERE [PONo] = " & Me.PONo

[ssanfu]

I simplified things a little bit for the post.

That really doesn't help.

The D1 field in the table is formatted as currency, with decimal places on Auto.

Formatting only affects how the data is displayed. Open the table in design view. What is the field type? Text or Number?

...= "$0.00"

If "D1" is a text type field, then you would use the quote delimiters.
If "D1" is a number type field, no delimiters are required.

Not sure about the parentheses, ..

Should not have the parentheses.

Tables![tblPO]![D1]

You cannot reference a field in a table like that.
If you want to "go straight to the table", you have to open a record set based on the table or query.

"Run time error 424 Object required".

This is telling you you need a recordset.



Without seeing the current code, or the record source for the form, you might try this: (AIR CODE)

If Me.D1 = 0.00 Then   ' D1 is the list box
CurrentDb.Execute "UPDATE tblPO SET [D1] = " & Me.Cost & " WHERE [PONo] = " & Me.PONo
Me.D1.Requery
ElseIf Me.D2 = 0.00 Then
CurrentDb.Execute "UPDATE tblPO SET [D2] = " & Me.Cost & " WHERE [PONo] = " & Me.PONo
Me.D2.Requery
'And so on, through D15

This is if there are 15 list boxes on the form?????

Can you post a picture of the form?
Can you post the dB? Even without the data?

[NISMOJim]

I plugged in your code again & got a data type mismatch. After more digging around, I found that the problem wasn't with the D1 field, but the PONo field. Since the PO Number is a 10 digit number, it is a text field instead of a Long Integer number field. I added the quotes around the end of the Update line of code like so...

If Me.D1 = 0 Or IsNull(Me.D1) Then
CurrentDb.Execute "UPDATE tblPO SET tblPO.D1 = " & Me.Cost & " WHERE [PONo] = '" & Me.PONo & "'"

Me.D1.Requery

Now it is working perfectly. Thank you for keeping me digging instead of dropping the whole thing.

I now see one formatting problem left that I do run into sometimes, and haven't figured out how to fix it.
The D1 through D15 fields are numbers formatted as currency in the table, and the related query, and show up as currency on the form. There is another listbox (DTot) on the form to total how much has been spent (totals D1 through D15) from the query with this as a Row Source...

SELECT qryPOInfo.PONo, qryPOInfo.D1, qryPOInfo.D2, qryPOInfo.D3, qryPOInfo.D4, qryPOInfo.D5, qryPOInfo.D6, qryPOInfo.D7, qryPOInfo.D8, qryPOInfo.D9, qryPOInfo.D10, qryPOInfo.D11, qryPOInfo.D12, qryPOInfo.D13, qryPOInfo.D14, qryPOInfo.D15, Sum(Nz([D1])+Nz([D2])+Nz([D3])+Nz([D4])+Nz([D5])+Nz([D6])+Nz([D7])+Nz([D8])+Nz([D9])+Nz([D10])+Nz([D11])+Nz([D12])+Nz([D13])+Nz([D14])+Nz([D15])) AS DTtl FROM qryPOInfo GROUP BY qryPOInfo.PONo, qryPOInfo.D1, qryPOInfo.D2, qryPOInfo.D3, qryPOInfo.D4, qryPOInfo.D5, qryPOInfo.D6, qryPOInfo.D7, qryPOInfo.D8, qryPOInfo.D9, qryPOInfo.D10, qryPOInfo.D11, qryPOInfo.D12, qryPOInfo.D13, qryPOInfo.D14, qryPOInfo.D15 HAVING (((qryPOInfo.PONo)=[Forms]![Del]![PONo]));

This listbox shows the correct total, but is not showing as currency, only as a number to 2 decimal places. Since I can't format a listbox, how can I get it to display as currency like the other listboxes are? All of them are coming from the same source, and I haven't found any difference in the properties.

P.S. Don't let that "Competent Performer" under my name fool you! I'm anything but competent!

[NISMOJim]

One more quick question just for my own understanding. Is there an advantage to using a textbox on a form with a Control Source from a query or table, over a listbox with a query built into the Row Source? I have never used the list boxes before, and it seems much more difficult to figure out what someone was trying to do when they put the database together. Which, if either, is a better choice?

[ssanfu]

The D1 field in the table is formatted as currency, with decimal places on Auto.

Again, formatting only affects how the data looks, not how the data is stored. I do not bother setting the format of fields in tables. Actually, I think setting a format in a table field is a waste if time. No user will (should) ever see a table, so it doesn't matter what the data looks like.
I do the formatting in the controls on the form.

I'm going to guess that the data type of the fields D1 - D15 are Number/Single????

--------------------------
I have a question. You have 15 unbound list boxes on a form. Somewhere you have code that look like this:

   If Me.D1 = 0 Or IsNull(Me.D1) Then
      CurrentDb.Execute "UPDATE tblPO SET tblPO.D1 = " & Me.Cost & " WHERE [PONo] = '" & Me.PONo & "'"
      Me.D1.Requery
   ElseIf Me.D2 = 0 Or IsNull(Me.D2) Then
      CurrentDb.Execute "UPDATE tblPO SET tblPO.D2 = " & Me.Cost & " WHERE [PONo] = '" & Me.PONo & "'"
      Me.D2.Requery
      .
      .
      .
   ElseIf Me.D15 = 0 Or IsNull(Me.D15) Then
      CurrentDb.Execute "UPDATE tblPO SET tblPO.D15 = " & Me.Cost & " WHERE [PONo] = '" & Me.PONo & "'"
      Me.D15.Requery

   End If

Lets say "Cost" = $10.50 and list box D4 = 0 and D8 = 0.
When the above code is executed, D4 is updated from $0 to $10.5. Then the code exits and list box D8 is left at $0.
Is this correct?


--------------------
Formatting in a list box/combo box......
Is listbox "DTot" displaying only the TOTAL of all of the fields? Or does "DTot" display fields D1 - D15 and the total?

Create a new list box next to "DTot" and paste in the following for the Row Source

SELECT qryPOInfo.PONo, Format(Sum(Nz([D1])+Nz([D2])+Nz([D3])+Nz([D4])+Nz([D5])+Nz([D6])+Nz([D7])+Nz([D8])+Nz([D9])+Nz([D10])+Nz([D11])+Nz([D12])+Nz([D13])+Nz([D14])+Nz([D15])),"currency") AS DTtl
FROM qryPOInfo
GROUP BY qryPOInfo.PONo
HAVING (((qryPOInfo.PONo)=[forms]![Del].[PONo]));

Set the column count to 2
The total should be the same as "DTot", but formatted as currency.


--------------------

Thank you for keeping me digging instead of dropping the whole thing.

Not a problem. You are learning how to debug!

--------------------
(From your post #9)

Is there an advantage to using a textbox on a form with a Control Source from a query or table, over a listbox

A text box and a list box are two different animals.

If a text box is bound, it is bound to a field in the form record source. It cannot be bound to a separate table or query.
If a text box is un-bound, you can set the control source to a function that will return a value that is not necessarily part of the form record source. The function can be a built in one (like DLOOKUP()) or a UDF (User Defined Function - a custom function you wrote).

A list box is kind of like a text box on steroids. A list box also has a control source and can be bound or un-bound, but it also has another property that the text box does not have: a Row Source. The row source allows you to have a fixed list ("Value List") or a query to provide a list of values to pick from.

So, is there an advantage of using a text box over a list box? It depends on what you are trying to do.

Clear as mud??

[NISMOJim]

The listbox is now formatted as currency, thank you for that.
I just opened the form & tried it out, and found a problem. Even though the D1-D8 listboxes show the prices from D1-D8 fields in the table, (the rest are still empty), the code is adding the value I enter in the Cost textbox into D1. I added a Debug.Print Me.D1 at the start of the code, and the Immediate window says Null. This tells me that even though the listbox shows a value, the box is really Null, which I just don't get!

Lets say "Cost" = $10.50 and list box D4 = 0 and D8 = 0.
When the above code is executed, D4 is updated from $0 to $10.5. Then the code exits and list box D8 is left at $0.
Is this correct? Sorry, I don't know how you got my post to show up in a box.

Yes, that is how it is supposed to work. I thought that is what it was doing last night. I changed D1 in the table to "0", then when I entered a value in the Cost textbox, it showed up in the D1 listbox, and changed D1 in the table like I wanted. The next entry should have showed up in D9, being the next field that is not zero or null, but I ran out of time to test it out until today, finding the problem above.

[NISMOJim]

OK, so I'm not having any luck getting the values from the form, so it's back to the table. I added the following code...

Dim rst As Recordset
Dim strSQL As String
strSQL = "SELECT * FROM tblPO WHERE tblPO.PONo = '" & Me.PONo & "'"
Set rst = CurrentDb.OpenRecordset(strSQL)

If rst("D1") = 0 Or IsNull(rst("D1")) Then
CurrentDb.Execute "UPDATE tblPO SET tblPO.D1 = " & Me.Cost & " WHERE [PONo] = '" & Me.PONo & "'"
Me.D1.Requery
ElseIf rst("D2") = 0 Or IsNull(rst("D2")) Then
CurrentDb.Execute "UPDATE tblPO SET tblPO.D2 = " & Me.Cost & " WHERE [PONo] = '" & Me.PONo & "'"
Me.D2.Requery
etc,etc

AND IT WORKS!

Thank you again for pointing me in the right directions, and for not giving up on me when I was about to give up on it.

[ssanfu]

Sorry, I don't know how you got my post to show up in a box.

If you are in the advances editor, ckick on the '#' in the menu. If you are in the regular editor, you would type
[ c o d e ] then the code and end it with [ / c o d e ] ...... <-- remove the spaces in the brackets [ ].
I had to enter the spaces so you could see the tags.

Because you have 15 fields that start the same (D) followed by numbers, you can use a For..Next loop. The 15 If statements can be replaced with the following code:

   Dim DFld As Integer
   Dim rst As Recordset
   Dim strSQL As String

   strSQL = "SELECT * FROM tblPO WHERE tblPO.PONo = '" & Me.PONo & "'"
   '         Debug.Print strSQL
   Set rst = CurrentDb.OpenRecordset(strSQL)

   For DFld = 1 To 15
      'use the NZ() function to change the NULL to a zero
      If Nz(rst("D" & DFld), 0) = 0 Then
         strSQL = "UPDATE tblPO SET tblPO.D" & DFld & "  = " & Me.Cost & " WHERE [PONo] = '" & Me.PONo & "'"
         '         Debug.Print strSQL
         CurrentDb.Execute strSQL
         Me("D" & DFld).Requery
         Exit For
      End If
   Next

   'close the recordset
   rst.Close
   Set rst = Nothing

Nothing wrong with your code, just less typing.

And its great you were able to figure out a solution.