Help with Macro or VBA (1 Viewer)

[LaurensDL]

Hi guys! I need some help..

I've created a stock managment database that will be used by a laboratory I work for... However, I'm stuck on the last implementation, and that's where you guys come in...
Side Note: As this is a big and ambitious project, it's also my first. So bare with me.

Let's first get the context right what I am working with..
To start of simple I have created a list of products (tbl_stk_Productenlijst). Users fill in a form (frm_stk_IngaveStock) with a subform (sfrm_stk_IngaveStock) to add or remove products from the inventory. The form and subform is connected respectively to tbl_stk_Transacties and tbl_stk_TransactieDetails. This works perfectly and is used to add or deplete products sush as gloves, masks, chemicals.

Apart from entering 'normal' products, there is also a form for preparing preparations. The form is bound to a SELECT query (qry_ber_Aanmaakbereiding) that combines two tables (tbl_ber_AanmaakProducten and tbl_ber_Recepten). There are also some calculated fields in this query that calculate the amount of product (found in the recipes, these building blocks are then found in the list of Products) used. More on that later.

On the form where the input is given for the preparation, I have put a button with an 'on click' event. This should be either a Macro or VBA. The code is as follows:

DoCmd.OpenForm "frm_stk_IngaveStock", acNormal, "", "", acAdd, acNormal #This opens the form wich is used for all other stock inputs
Forms!frm_stk_IngaveStock!Per_INI = Forms!frm_ber_AanmaakBereiding!Per_INI #This passes through the same coworker
Forms!frm_stk_IngaveStock!Trs_TransactieType = 2 #This sets the transaction as a depletion
Forms!frm_stk_IngaveStock!sfrm_trs_IngaveStock.Form!Prd_ID = Forms!frm_ber_AanmaakBereiding!Rcp_Prd1 #This passes trough the first product of the recipe
Forms!frm_stk_IngaveStock!sfrm_trs_IngaveStock.Form!Trs_Hoeveelheid = Forms!frm_ber_AanmaakBereiding!rcp_Prd1QtyP #This passes trough the used amount of the first product
DoCmd.RunCommand acCmdSaveRecord #Just as a placeholder, will be deleted

The code above works as it should be. The problem occurs in the following, with the other product: As a path I have either used [Forms]![frm_stk_IngaveStock]![sfrm_trs_IngaveStock] and [Forms]![frm_stk_IngaveStock]![sfrm_trs_IngaveStock], both result in the error : The object '[Forms]![frm_stk_IngaveStock]![sfrm_trs_IngaveStock]' isn't open.
In essence I just want to add a new record to the subform and pass the data from the next product in the recipe. There is a max of 6 products per recipe. This is the code I used...

Me.[Forms]![frm_stk_IngaveStock]![sfrm_trs_IngaveStock].SetFocus
DoCmd.GoToRecord acForm, "[Forms]![frm_stk_IngaveStock]![sfrm_trs_IngaveStock]", acNewRec
If (Eval("[Forms]![frm_ber_AanmaakBereiding]![Rcp_Prd2] Is Not Null")) Then
Forms!frm_stk_IngaveStock!sfrm_trs_IngaveStock.Form!Prd_ID = Forms!frm_ber_AanmaakBereiding!Rcp_Prd2
Forms!frm_stk_IngaveStock!sfrm_trs_IngaveStock.Form!Trs_Hoeveelheid = Forms!frm_ber_AanmaakBereiding!rcp_Prd2QtyP
End If

Can someone help me please? I'm sorry the names of the access objects are in Dutch

 

[theDBguy]

Hi. Welcome to AWF!

Are you able to share a sample db with test data?

 

[June7]

have either used [Forms]![frm_stk_IngaveStock]![sfrm_trs_IngaveStock] and [Forms]![frm_stk_IngaveStock]![sfrm_trs_IngaveStock]

Look the same to me.

Instead of Eval() function and Is Not Null, use: Not IsNull([Forms]![frm_ber_AanmaakBereiding]![Rcp_Prd2])

 

[LaurensDL]

The file is too large and download links are not allowed. Is there another way to transfer the DB?

 

[LaurensDL]

Look the same to me.

Instead of Eval() function and Is Not Null, use: Not IsNull([Forms]![frm_ber_AanmaakBereiding]![Rcp_Prd2])

My bad was meant to type:
have either used [Forms]![frm_stk_IngaveStock]![sfrm_trs_IngaveStock] and [Forms]![sfrm_stk_IngaveStock]

 

[theDBguy]

The file is too large and download links are not allowed. Is there another way to transfer the DB?

If you want to post a download link, don't make it a hyperlink. Exactly how big is it? Can you pare it down to just the essentials? Thanks.

 

[LaurensDL]

about 137 MB the link is:we.tl/t-de2pL6EgPU

Make sure to unblock in the setting

 

[Pat Hartman]

Hi, welcome to AWF
I'm not sure I'm following what you are doing so let me describe how I do something similar. It sounds like you are formulating an actual "batch" and you want to also adjust the inventory.

I did something similar for Clairol. Our products at the facility were shampoo, cream rinse, and hair dyes. The formulas were all stored as percents. So water was 43&, chemb was 22%, etc. Then when we got an order for 100 pounds, we would calculate the weights by multiplying the percent times the batch size.

In that case, the main form defines the batch and the size and the name of the formula, etc. When you save the main form record, you can run an append query that creates the detail records and calculates the amount of the chemical being used for each. The user should not have to actually type in amounts.

If your formula doesn't specify the amount for each component, then you would just append records with 0 as the quantity and rely on the users to fill in the amounts.

 

[arnelgp]

from which form do you have a button that has the code:

DoCmd.OpenForm "frm_stk_IngaveStock

 

[June7]

It's in the general module and behind frm_ber_AanmaakBereiding.

Posted code is not quite the same as either procedure.

Laurens, can't use Me reference in a general module. That will trigger a compile error. That function is not called by another procedure, maybe a macro does.

What steps should we follow to replicate issue?

 

[LaurensDL]

Fixed it!

I've changed the Eval() function to the Not IsNull

And the problem was mainly the brackets... Didn't know VBA did not recognise these for some reason.

Also the lost the me. And just used forms!frm_stk_IngaveStock!sfrm_stk_IngaveStock.SetFocus


Thanks for the support guys!

 

[June7]

VBA certainly does accept brackets in most cases. But not in form name for either DoCmd method.