Set Value on Subform

[alexxmf]

Hi all,

I am new to the forum and to Access. I am building a simple database to control stock and sales at my wife's new shop. I have managed to google my way through most of my issues, but I've been banging my head against the wall over this one for a day or two now.

All I want to do is set the value property of a text box in a subform. I know I am referencing the control correctly, because I am doing a calculation using the same control.

The control on the subform is txtNoInStock (this contains info on how many of a certain item are in stock) and the control on the main form is txtAmountSold (this is how many of a particular item are about to be sold). If I do the following calculation to check if there are enough in stock:

If [Forms]![frmSale]![DS].[Form]![txtNoInStock]-[txtAmountSold]<0

It works. I generate a text box to tell the user there are not enough in stock. However, if there are enough in stock, I simply want to subtract the amount in txtAmountSold from the subform control. Kind of like:


SetProperty

Control Name - [Forms]![frmSale]![DS].[Form]![txtNoInStock]

Value =[Forms]![frmSale]![DS].[Form]![txtNoInStock]-[txtAmountSold]

But I always get an error saying that the control is misspelled or doesn't exist!

I hope this is explained well enough and isn't TLDR.

Thanks in advance.

 

[sneuberg]

Please show the actual code that is giving you this error. Also I don't understand what you want to do. Do you want to display [Forms]![frmSale]![DS].[Form]![txtNoInStock]-[txtAmountSold] in a textbox or have it displayed by MsgBox

 

[alexxmf]

Hi,

Sorry for the confusion.

I use the macro builder to create code and there is no way to select all. I have taken a screenshot of the macro builder code and attached it. I would like the text box value of the control on the subform to be updated.

Regards,
Alex.

 

[sneuberg]

Sorry I never use macros and so I don't know why this isn't working. I'd do it differently something like.

If [Forms]![frmSale]![DS].[Form]![txtNoInStock] - [txtAmountSold] < 0 Then
    MsgBox "Not enough in stock"
Else
    CurrentDb.Execute "UPDATE .........." 'Update the current record in the subform
    MsgBox "Added to Receipt"
    [Forms]![frmSale]![DS].[Form].Requery
    
End If

If you zip and upload your database so I know what the tables and fields involved are I will give you the exact code.

 

[alexxmf]

I have zipped and attached the file. The layout of the form is a work in progress, as are the records ( I am trying to address the functionality of the database before the layout). The form where the problem lies is frmSale.

I would ideally like to know the solution using macros so I can troubleshoot if I come to an error later in the project, but as long as I can get the function working, I will be happy.

Thanks for your time.

Regards,
Alex.

 

[sneuberg]

Edit: See Post 8 First


The code would be

If [Forms]![frmSale]![DS].[Form]![txtNoInStock] - [txtAmountSold] < 0 Then
    MsgBox "Not enough in stock"
Else
    CurrentDb.Execute "UPDATE [tblStock] SET [NumberInStock] = " & [Forms]![frmSale]![DS].[Form]![txtNoInStock] - Me.txtAmountSold & _
        " WHERE [StockID] = " & [Forms]![frmSale]![DS].[Form]![StockID]
    Me.DS.Requery
     MsgBox "Added to Receipt"
End If

You would need to add StockID to the frmSale record source. Just check the checkbox in grid. But I'll see I can put this is a macro for you. I will get back to you when I do or don't.

 

[sneuberg]

Edit: See Post 8 first.

To do this with a macro you will first need to create an update query as shown in the attached qryUpdateNumberInStock screen shot. The SQL for this query is:

UPDATE tblStock SET tblStock.NumberInStock = [forms]![frmSale]![DS].[Form]![txtNoInStock]-[Forms]![frmSale]![txtAmountSold]
WHERE (((tblStock.StockID)=[Forms]![frmSale]![DS].[Form]![StockID]));

For this query to work the StockID needs to be added to the qrySearchStockBySerialsSub query. See the attached screen shot.

The macro code is shown in the attached Macro Screen Shot. I took the other code you had in this macro out so that I could test it. I didn't try to figure out what you were trying to do with the rest of that code.

I would bet that sooner or later you will get tired of fighting with these macros and learn VBA. I also bet that when you get to that point you'll regret not having made the switch earlier. If you want to get started with VBA I suggest this YouTube Series

 

[JHB]

You need to use the Action "SetValue" and not "SetProperty".

Click the button "Show All Actions"

 

[alexxmf]

Excellent, works a treat, thank you!

What is the difference between the two actions 'SetProperty -> Value' and 'SetValue'? I noticed it had a warning symbol next to SetValue when I used it.

sneuberg:

I will look into the VBA. I didn't go down that route as I didn't want to have to learn too much just to write one database.

Thanks for your time, really appreciated.

Alex.

 

[JHB]

Excellent, works a treat, thank you!

What is the difference between the two actions 'SetProperty -> Value' and 'SetValue'? I noticed it had a warning symbol next to SetValue when I used it.

Property is more how things looks.
Set value - I think the word is self explaining!
The warning symbol is for alert you that you're going changing the data values.

 

[gemma-the-husky]

one other thing.

it is not best practice to actually store the number of items that are in stock. Although it seems like it is, the better way is to sum all the movements to a given point in time, both in and out, and get the stock on hand that way.

you can end up writing a lot of stuff to manage the current on hand quantity, and easily miss one. Let's say you delete a sale from a table for some reason. This means that your on hand quantity is now wrong.

If you calculate it whenever you need it, there is never an issue. It might seem a longer way round, but it is actually more efficient.