Passing parameters from one form to another

  • Thread starter Thread starter Scott A
  • Start date Start date
S

Scott A

I trying to do something that I think requires a value to
be passed from one form to another - I tried setting this
up using the link criteria - but the form I'm launching
isn't bound to any data... I'm working on passing the
parameter using OpenArgs, but am not having much success
as of yet.

Here's a little more detail:

I'm essentially trying to set up a transaction form - not
with customers and products, but it's very similar. The
table I need to populate with this form is just like a
customer order table.

So - I have a link from the 'customer' form to
this 'customer orders' form. What I'm trying to do is
show a list of 'products' that the customer can order.
I'm using a list box to show all products on the page.
The list box uses a SQL statement as a row source,
displaying three fields (as columns) from the products
table.

Problem: I need to show as selected, any 'products' that
have already been purchased, and am trying to do so by
querying the database and finding any matches in the
customer order table. I'm thinking that this is not
working because the transaction form that contains the
list box isn't bound to any data - and am trying to pass
the key field using the button that launches the form.

Here's the code:
=========================================================
'Customer' form OnClick Event (customer = SOP)
I intend this code to launch the orders form and pass the
SOPID...
=========================================================
Private Sub cmdDistributionForm_Click()
On Error GoTo Err_cmdDistributionForm_Click

Dim stDocName As String

stDocName = "frmDistribution"
DoCmd.OpenForm stDocName, , , , , acDialog, Me!SOPID


Exit_cmdDistributionForm_Click:
Exit Sub

Err_cmdDistributionForm_Click:
MsgBox Err.Description
Resume Exit_cmdDistributionForm_Click

End Sub
=========================================================
Customer Orders form OnLoad Event (Orders = Distributions)
This code is supposed to look at the existing 'products
ordered' (Distributions), and update the list box with any
items already in the database. I'm thinking this code is
not working too well, and that I haven't set it up in a
way that correctly catches the value passed from the
customer form...
==========================================================
Private Sub Form_Load()

Dim dbsSOP As DAO.Database
Dim dynRecipientsChosen As Recordset
Dim strSql As String
Dim intCurrRecipient As Integer
Dim varSOPID As Variant

varSOPID = Me.OpenArgs

'-- Open query to create list of Recipients already
assigned SOP, create dynaset
Set dbsSOP = CurrentDb
strSql = "SELECT tblDistributions.RecipientID FROM
tblDistributions" & _
"WHERE (tblDistributions.SOPID=varSOPID);"
Set dynRecipientsChosen = dbsSOP.OpenRecordset(strSql,
dbOpenDynaset)

'-- Loop through the selected choies
Do Until dynRecipientsChosen.EOF

'-- For each of the items in the listbox, see if
it matches any
'-- of the recipients already assigned the SOP
For intCurrRecipient = 0 To Me!
lboRecipientList.ListCount - 1

'-- If there is a match, mark it in the list
box as selected
If dynRecipientsChosen!RecipientID = _
Me!lboRecipientList.ItemData
(intCurrRecipient) Then
Me!lboRecipientList.Selected
(intCurrRecipient) = True
Exit For
End If

Next intCurrTable

dynRecipientsChosen.MoveNext

Loop

End Sub

========================================================

Many thanks to anyone willing to contribute suggestions to
the modification of this code.

Scott
 
One way to get around this issue as I also deal with unbound forms is to
setup a string variable data type to record the ID number of all of the
products from your 'Customer' form. There's a couple of ways to do this,
either setup the variable as a Public variable which then your 'Customer
Order' form refers to this public variable, or the string variable could be
a private one and it gets passed via a sub/function call. In your case, it
probably would be easier just to setup the variable as a public variable.

Word of Warning about the Multi-Extended Selection Mode and possibly the
Simple-Extended Selection Mode of a List Box Object. The Count Property of
<Listbox>.ItemsSelected collection, and each of the
<Listbox>.Selected(<item>) Property on all items does not get updated until
AFTER an update has taken place on the control. When I created my Listbox
Extension Class for adding a drag and drop feature to my listboxes, I had to
deal head on with this very issue. Given this, we could use the LostFocus
Event on the listbox to assign the string variable like the following code:

Public strProductsSelected as String
Dim item as Variant
strItemsSelected = ""
For each item in <Listbox>.ItemsSelected
strProductsSelected = strProductsSelected & ";" &
item.Column(<Index>,item)
Next
strProductsSelected = VBA.Strings.Mid(strProductsSelected,2)

Then in your other form's GotFocus Event, you could use something like the
code shown below, provided that the listbox in the 'Customer Order' form
DOES NOT have the focus, cause the Count Property that I referred to above,
it will show 0 at the time it gets the focus regardless if there's items
selected or not, and none of the Selected Property will be shown as selected
while the listbox has the focus. Given this very issue that I have with
listboxes, I had to create some string functions in addition just to have a
list of items what's solidly selected and what DOESN'T remain selected to
account for the different methods of selecting/deselecting items via both
keyboard and mouse. Therefore, it's important to ONLY have the following
code ran when the listbox DOES NOT have the focus, else you may very well
get unexpected results.

dim strCustItems As String, I as Long, L as Long, X as Long, J as Long
strCustItems = strProductsSelected, strCurItem as String
X = <Listbox>.ListCount
For I = 0 to X - 1
<Listbox>.Selected(I) = 0
Next I
L = VBA.Strings.Len(strCustItems)
I = InStr(1,strCustItems,";",0)
Do While I > 0
strCurItem = Left(strCustItems,I-1)
For J = 0 to X - 1
If <Listbox>.Column(<Index>,J) = strCurItem Then
<Listbox>.Selected(J) = -1
Exit For
End If
Next J
strCustItems = Right(strCustItems,L-I)
I = InStr(1,strCustItems,";",0)
Loop
If strCustItems <> "" Then
For J = 0 to X - 1
If <Listbox>.Column(<Index>,J) = strCustItems Then
<Listbox>.Selected(J) = -1
Exit For
End If
Next J
End If

<Listbox> is the list box object that you are working with.
<Index> is the column number of the column that your Product ID number is in
within the list box object.

Note: The way selecting/deselecting items via code is done is slightly
different while the list box has the focus as compared to when the list box
doesn't have the focus.

When a listbox has the focus:
The -1 on the Selected Property changes the state from either True to
False OR False to True
The 0 on the Selected Property does absolutely nothing to the item.

When the listbox does NOT have the focus:
The -1 on the Selected Property selects the item.
The 0 on the Selected Proerty deselects the item.
 
Back
Top