Using forms to immediately edit/update a table

[TKTheKid]

Hi all,

Please see the attached database. What I'm asking for is probably simple for most of you, but is tricky for a newbie like me.

If you take a look at formWarehouseData, you'll (hopefully!) get an idea of what I want/am trying to do:

I'd like to be able to use the three combo boxes to select a ticket, location and article from tableWarehouseData. This should display the quantity for that ticket/location/article under "The current quantity is:." Then, I would be able to enter a new quantity for that ticket/location/article under "The quantity will be changed to:", click the OK button on the right and have that update tableWarehouseData for the selected ticket/location/article immediately.

For example, if I selected ticket 1111A1, location 90003 and article 9326302909, 6 would appear as the current quantity. If I entered 77 and clicked OK, then 77 should become the new value for ticket 1111A1, location 90003 and article 9326302909.

Any help is appreciated!

Thank you!

Tony

[June7]

There is no data in the Ticket and Loc fields.

The two QTY labels should be textboxes. Also, command buttons are usually used for what you have as OK and Clear labels.

Options:

1. because the form is not bound to data, use code such as DLookup to retrieve the quantity and then use an UPDATE sql action to save the revised value

2. bind the form and filter the form recordset to the desired record, review http://datapigtechnologies.com/flash...tomfilter.html
then you can type whatever you want into textbox bound to quantity field

[TKTheKid]

Thanks June.

Well, actually there is some data in the Ticket and Loc fields. It's just that they're way, way at the bottom. Which I guess now brings up another question. How do I make the combo boxes only show unique values from tableWarehouseData?

I would much more prefer option 2 that you listed above. And the video you sent did help a little, but I still feel lost.

How can I make it so that as I select data from each combo box, it only allows me to select relating data in the other combo boxes. As in my example in my first post, if I selected ticket 90000, then only 1111A1 and 1114A1 should be available for me to select in the Location combobox. Then when I selected either of those locations, only 9326302909 would be available to select in the Article combobox. Then of course, I would want the quantity for that ticket/location/article under "The current quantity is:." to appear and be able to click OK to update the new quantity I entered to tableWarehouseData.

And I know that OK and Clear need to be command buttons, but I actually read somewhere that you don't even have to use VBA. That you can just have that new value updated to the table some other way. Is that true? This doesn't sound right to me though haha.

By the way, attached is an update to the database I attached earlier.

Thank you,
Tony

[June7]

Sorry, guess I should have kept scrolling the table.

Probably what you read about is bound form. If form is bound to the table and textbox bound to field, then entry/edit to textbox feeds directly to table. This is basic Access functionality and no code needed. Search Access Help or google on topic: Access bound form.

You are describing dependent (cascading) comboboxes. A little code needed. There is another DataPigTech tutorial. Here is the page with all tutorials listed http://datapigtechnologies.com/AccessMain.htm

[TKTheKid]

Okay, I've made a bit of progress with the dependent comboboxes. Please see form formWarehouseDataGOOD. Now when I drop down the TICKET combobox, it shows only the distinct values. I've also made that selection affect the source for the LOC combobox.

But now, how do I have what was entered into the newQuantity text box feed directly to tableWarehouseData to overwrite/replace the quantity that is already there for that record?

Thanks!

[June7]

If the embedded macro is correctly filtering the recordset, then type whatever you want to in the Quantity textbox.

Put the unbound search boxes in the form Header section. Select all the controls and right click > Layout > Remove, then you can move, resize controls as you want.

[TKTheKid]

If the embedded macro is correctly filtering the recordset, then type whatever you want to in the Quantity textbox.

Put the unbound search boxes in the form Header section. Select all the controls and right click > Layout > Remove, then you can move, resize controls as you want.

Hmm, I'm not sure I fully understand what you're saying there, but I came to the realization that I'm going to have to use some VB after all.

What I really need is to be able to have a user select a ticket, then a location to see the current quantity for the article (which is how formWarehouseDataGOOD is now). Then for the user to be able to enter a new quantity in a text box and click on a command button to have the new quantity update the quantity for that record in tableWarehouseData.

The key thing though would be to have a message box or something pop up after the user clicked the command button to ask them, "Are you sure you want to update the quantity of ticket 90000, location 1111A1 to X?" Then they could select "Yes" or "No" to confirm their choice or cancel. I hope this is as simple as it seems in theory! Haha.

Thank you very much for all of your help/patience!

Tony

[June7]

Exactly what did you not understand about those two statements?

Yes, a little code is required. I prefer VBA.

Why have user click a button for entry of new quantity? The entry can be directly into the bound textbox and unbound box is unnecessary. If you want to annoy user with a verification message, then use the BeforeUpdate event of textbox. http://www.blueclaw-db.com/access_ev...foreupdate.htm

If MsgBox("Do you really want to replace old quantity " & Me.textbox.OldValue & " with " & Me.textbox & "?", vbYesNo) = vbNo Then
Cancel = True
Undo
End If

If you really must use unbound textbox and button, then code in the button click event would be:
If MsgBox("Do you really want to replace old quantity?", vbYesNo) = vbYes Then
Me.Quantity = Me.Qty
End If

[TKTheKid]

Unfortunately, I really do have to annoy the user with a text box like that for paranoia and double-checking purposes.

So I attached my updated database again. Can you please take a look at formWarehouseData? On click for my command button, I have:

Private Sub Command55_Click()

tableWarehouseData.QUANTITY = "Select newQuantity.Value " & _
"WHERE comboTicket.Value = tableWarehouseData.TICKET & comboLocation.Value = tableWarehouseData.LOC & boxARTICLE.Value = tableWarehouseData.ARTICLE;"

End Sub

When I click the command button, I get a "Run-time error '424': Object required." How do I fix this? Am I far off? Just to refresh things, I want the user to be able to select a ticket, location and article, see the current quantity for it, then enter a new value in the textbox and click the command button to have that value overwrite the value in tableWarehouseData.

Also, thank you for your help above on the message boxes. Where exactly would I put your example of:

If MsgBox("Do you really want to replace old quantity?", vbYesNo) = vbYes Then
Me.Quantity = Me.Qty
End If

Would it just go at the end of my Command55_Click subroutine?

Thanks!

[June7]

Your Command55 code doesn't make sense. Can't change value in table like that. The SQL would be an UPDATE, not SELECT. However, no SQL action is even needed.

Private Sub Command55_Click()

If MsgBox("Do you really want to replace old quantity?", vbYesNo) = vbYes Then
Me.Quantity = Me.newQuantity
End If

End Sub

[TKTheKid]

Ah, that worked! Thank you June! I feel like that was the most significant step. Now the rest should be simpler to deal with.

Speaking of simple, I now want to have the source of the ARTICLE combobox (comboArticle) to change based on the user's selection from the Location combobox (comboLocation). So all I did was use my comboTicket_AfterUpdate subroutine as a basis to make my comboLocation_AfterUpdate subroutine. This is what those look like:

Private Sub comboTicket_AfterUpdate()

comboLocation.RowSource = "Select DISTINCT tableWarehouseData.Loc " & _
"FROM tableWarehouseData " & _
"WHERE tableWarehouseData.Ticket = " & comboTicket.Value & " " & _
"ORDER BY tableWarehouseData.Loc;"
End Sub

Private Sub comboLocation_AfterUpdate()

comboArticle.RowSource = "Select DISTINCT tableWarehouseData.Article " & _
"FROM tableWarehouseData " & _
"WHERE tableWarehouseData.Loc = " & comboLocation.Value & " " & _
"ORDER BY tableWarehouseData.Article;"
End Sub

The Location combobox correctly shows only locations based on the user's selection in the Ticket combobox, but the same is not true for the Article combobox. Selecting a ticket and location is fine, but once the Article combobox is clicked it prompts the user for a parameter value or spits out an error:

Syntax error (missing operator) in query expression" 'tableWarehouseData.Loc = 111A1

It looks like I have the VB code correct, but apparently not haha. What am I missing? The updated database is attached.

Thanks!

Tony

[June7]

LOC field is text type so need apostrophe delimiters for criteria:

"WHERE tableWarehouseData.Loc = '" & comboLocation.Value & "' " & _

[TKTheKid]

Oh, okay. I should have spotted that, haha!

But oh, shoot. That does fix the issue with the correct articles appearing in comboArticle, but now the correct QUANTITY does not appear for the selected ticket/location/article. Why did that all of a sudden change?

I've attached the database again after making the latest change you pointed out with the apostrophes.

Thank you!

[June7]

There is no code to go to the record that meets the criteria of the comboboxes. Didn't there used to be embedded macro in LOC combobox AfterUpdate event? Now you want to base the filter on Article number?

There are 6 records for Article 9326302909. Should Article be a unique value?

[TKTheKid]

Article is not a unique value, no. When I eventually use this database with "real" data, the same article will definitely be under multiple different tickets and locations. Which is why the value that pops up in the Quantity textbox needs to based on the selected ticket, location and article.

I'm guessing that that makes things really tricky?