Trouble passing arguments from 1 form to another

[brianv]

i have a form [Add an Ingredient] which has a List box [ListIngredient].
ListIngredient has 3 fields, IngredientId, Ingredient and Note. these are
the fields in tbl Ingredients.

Ingredients:
IngredientId autoNumber Primary Key
Ingredient text
Note text

what i am trying to do is update the data for a selected rcd. so i click a
rcd in the List box to select it. i then click a button which will execute
an event procedure. the procedure needs to open a form [Change Ingredient]
and pass the 3 values into it. i started off by just trying to pass 1 value.
this is what i have

stDocName = "Change Ingredient"
DoCmd.OpenForm stDocName, , , "[ChgIngredient]='" & [Forms]![Add an
Ingredient]!ListIngredient.Column(1) & "'"

this is in the code for the button on the first form. can you tell me what i
am doing wrong and what the syntax looks like to pass all 3 values. i plan
on hidding the IngredientId on the second form. allow the user to change the
values in the other fields and then push a button which will do the update.
i have never used Visual Basic or access before. if there is a better way, i
am all ears.


thanks


brian

 

[Nikos Yannacopoulos]

Brian,

Since the goal is to update an existing ingredient, you don't need to
pass all three fields to the other form! Just make the "Change
Ingredient" form bound (the ingredients table is the form's record
source, and the form controls are bound to the table fields), so opening
it at a specific record (which is what the Where clause in
DoCmd.OpenForm does) will bring up all fields for that ingredient; make
your changes and save the record.
Now, to the Where clause: the LHS of the expression needs to match the
name of the field in the undelying table, not the name of the control on
the form: change to:

stDocName = "Change Ingredient"
DoCmd.OpenForm stDocName, , , "[IngredientId]='" & _
[Forms]![Add an Ingredient]!ListIngredient.Column(0) & "'"

To make your code simpler, since it is stored in the form's own module,
you can reference the listbox by preceding its name with the Me.
keyword, instead of using the full reference:

stDocName = "Change Ingredient"
DoCmd.OpenForm stDocName, , , "[IngredientId]='" & _
Me.ListIngredient.Column(0) & "'"

Note also that I have changed the column index to 0, because it's a
zero-based index (the first column is 0; the second one is 1). Also,
assuming that the listbox bound column is the first one (IngredientId),
you don't need to specify the column at all, as the bound one will be
returned by default:

stDocName = "Change Ingredient"
DoCmd.OpenForm stDocName, , , "[IngredientId]='" & _
Me.ListIngredient & "'"

Finally, re. the IngredientId control on the second form, you can set
Visible property ot False and/or the Locked property to True, so the
user can't cjhange it accidentally.

HTH,
Nikos