Open AARRGGGs Help please

  • Thread starter Thread starter Darryn
  • Start date Start date
D

Darryn

Could anyone please tell me wher I am going wrong here with open args

I am trying to pass a ProgramID value from the form frmDetailProgInv
to a popup form and then use the multi selected list values in the
popup form to fill in a subform on frmDetailProgInv.

Here is my code
The open form button on my main form
Private Sub Command17_Click()
On Error GoTo Err_Command17_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmMultiselect"
DoCmd.OpenForm stDocName, , , stLinkCriteria, , acWindowNormal,
"Open Args"

Exit_Command17_Click:
Exit Sub

Err_Command17_Click:
MsgBox Err.Description
Resume Exit_Command17_Click
End Sub

The code to add entries from the popup form to the sub form

Private Sub cmdAddItemsToList_Click()
Dim vItem As Variant, db As DAO.Database
Set db = CurrentDb
For Each vItem In lstSelectItems.ItemsSelected
db.Execute "Insert into tblDetailGearList (ProgramID,
EquipRecordID)"
values (" & lProgramID & ", " & lstSelectItems.ItemData(vItem)&
")", dbFailOnError
Next vItem
End Sub

Private Sub Form_Load()
Dim lProgramID As Variant
lProgramID = Forms!frmDetailProgInv.OpenArgs
End Sub


I am getting a syntax error in this line of the cmdAddItemsToList
event.

values (" & lProgramID & ", " & lstSelectItems.ItemData(vItem)&
")", dbFailOnError

But I cant work out what is worng with my syntax

Darryn
 
Could anyone please tell me wher I am going wrong here with open args

I am trying to pass a ProgramID value from the form frmDetailProgInv
to a popup form and then use the multi selected list values in the
popup form to fill in a subform on frmDetailProgInv.

Here is my code
The open form button on my main form
Private Sub Command17_Click()
On Error GoTo Err_Command17_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmMultiselect"
DoCmd.OpenForm stDocName, , , stLinkCriteria, , acWindowNormal,
"Open Args"

Exit_Command17_Click:
Exit Sub

Err_Command17_Click:
MsgBox Err.Description
Resume Exit_Command17_Click
End Sub

The code to add entries from the popup form to the sub form

Private Sub cmdAddItemsToList_Click()
Dim vItem As Variant, db As DAO.Database
Set db = CurrentDb
For Each vItem In lstSelectItems.ItemsSelected
db.Execute "Insert into tblDetailGearList (ProgramID,
EquipRecordID)"
values (" & lProgramID & ", " & lstSelectItems.ItemData(vItem)&
")", dbFailOnError
Next vItem
End Sub

Private Sub Form_Load()
Dim lProgramID As Variant
lProgramID = Forms!frmDetailProgInv.OpenArgs
End Sub


I am getting a syntax error in this line of the cmdAddItemsToList
event.

values (" & lProgramID & ", " & lstSelectItems.ItemData(vItem)&
")", dbFailOnError

But I cant work out what is worng with my syntax

Darryn
Click to expand...

I see several problems. First, according to the code you posted, your
DoCmd.OpenForm call is passing the string literal "OpenArgs" as its
OpenArgs argument:
DoCmd.OpenForm stDocName, , , stLinkCriteria, , acWindowNormal,
"Open Args"
Click to expand...

Perhaps you meant to write this:

DoCmd.OpenForm stDocName, , , stLinkCriteria, , _
acWindowNormal, Me!ProgramID

or something like that.

Second, in the Load event of form "frmMultiselect", you've declared
lProgramID at the procedure level. That means it won't be known to any
other procedures in that form's module. Declare it instead at the
module level, in the Declarations section of the form's code module.

Third, also in the Load event of form "frmMultiselect", you're not
picking up the value from the form's OpenArgs propery correctly. it
should be like this:

lProgramID = Me.OpenArgs

That is, you want *this* form's OpenArgs, not the OpenArgs value that
was passed to frmDetailProgInv.

Fourth, in cmdAddItemsToList_Click(), your SQL statement appears to be
broken onto two lines without a line-continuation character and
concatenation operator. Try this:

db.Execute _
"Insert into tblDetailGearList (ProgramID, EquipRecordID) " & _
"Values (" & lProgramID & ", " & _
lstSelectItems.ItemData(vItem) & ")", _
dbFailOnError

I don't know if I've spotted everything, but that should get you a lot
closer.
 
Darryn said:
Could anyone please tell me wher I am going wrong here with open args

I am trying to pass a ProgramID value from the form frmDetailProgInv
to a popup form and then use the multi selected list values in the
popup form to fill in a subform on frmDetailProgInv.

Here is my code
The open form button on my main form
Private Sub Command17_Click() [snip]
DoCmd.OpenForm stDocName, , , stLinkCriteria, , acWindowNormal,
"Open Args"
Click to expand...
[snip]

That should be more like:
DoCmd.OpenForm stDocName, _
WindowMode:= acWindowNormal, _
OpenArgs:= nameofIDtextbox



The code to add entries from the popup form to the sub form

Private Sub cmdAddItemsToList_Click()
Dim vItem As Variant, db As DAO.Database
Set db = CurrentDb
For Each vItem In lstSelectItems.ItemsSelected
db.Execute "Insert into tblDetailGearList (ProgramID,
EquipRecordID)"
values (" & lProgramID & ", " & lstSelectItems.ItemData(vItem)&
")", dbFailOnError
Next vItem
End Sub

Private Sub Form_Load()
Dim lProgramID As Variant
lProgramID = Forms!frmDetailProgInv.OpenArgs
End Sub


I am getting a syntax error in this line of the cmdAddItemsToList
event.

values (" & lProgramID & ", " & lstSelectItems.ItemData(vItem)&
")", dbFailOnError
Click to expand...

If that's on a line by itself, then you forgot the line
continuation on the previous line:

db.Execute "Insert into tblDetailGearList " _
& "(ProgramID,EquipRecordID)" _
& "Values (" & lProgramID & ", " _
& lstSelectItems.ItemData(vItem) & ")", _
dbFailOnError
 
On Mon, 3 Nov 2003 13:50:57 -0500, "Dirk Goldgar"

Hi Dirk & Marshall
Thanks for your help so far,
I see several problems. First, according to the code you posted, your
DoCmd.OpenForm call is passing the string literal "OpenArgs" as its
OpenArgs argument:


Perhaps you meant to write this:

DoCmd.OpenForm stDocName, , , stLinkCriteria, , _
acWindowNormal, Me!ProgramID
Click to expand...

Yes that was the aim, I also put a control on the form with this value
(hidden) as the user does not need to see it
Second, in the Load event of form "frmMultiselect", you've declared
lProgramID at the procedure level. That means it won't be known to any
other procedures in that form's module. Declare it instead at the
module level, in the Declarations section of the form's code module.
Click to expand...
I did this in the declarations section.
Option Compare Database
Dim lProgramID As Variant
Option Explicit
Third, also in the Load event of form "frmMultiselect", you're not
picking up the value from the form's OpenArgs propery correctly. it
should be like this:

lProgramID = Me.OpenArgs

That is, you want *this* form's OpenArgs, not the OpenArgs value that
was passed to frmDetailProgInv.
Click to expand...
I am not really sure what I need to be passing that is my problem,
another person gave me some suggestions and snippets of code which I
have been trying to get working. I need to get the ProgramID number
from the main form into the subform with each item selected in the
multi select list box.
Fourth, in cmdAddItemsToList_Click(), your SQL statement appears to be
broken onto two lines without a line-continuation character and
concatenation operator. Try this:

db.Execute _
"Insert into tblDetailGearList (ProgramID, EquipRecordID) " & _
"Values (" & lProgramID & ", " & _
lstSelectItems.ItemData(vItem) & ")", _
dbFailOnError I changed that section to this

I don't know if I've spotted everything, but that should get you a lot
closer.
Click to expand...

Can you suggest a good book to get which explains all of the hows and
whys of this stuff, I am using A97 but will be u/grading to A2003 soon

So far when I hit the compile button it seems ok
When I open the form and hit the open multiselect it opens
I then select a few items and hit the add Itesm button I get an error
91 object variable not set and the following code is highlighted

db.Execute _
"Insert into tblDetailGearList (ProgramID, EquipRecordID) " &
_
"Values (" & lProgramID & ", " & _
lstSelectItems.ItemData(vItem) & ")", _
dbFailOnError

When I hover the mouse over this section I can see the correct
ProgramID value
"Values (" & lProgramID & ", " & _

When this section is highlighted in yellow I can see the following
values
lstSelectItems.ItemData(vItem) & ")", _
dbFailOnError
When I hover the cursor over lstSelectItems.ItemData9vItem) it says:
lstSelectItems.ItemData(vItem)=EquipmentRecordID

but hovering over (vItem)=Empty

I know this means that it is not picking up the EquipmentRecordID's of
the selected items but I don't know why

Darryn
 
On Mon, 3 Nov 2003 13:50:57 -0500, "Dirk Goldgar"


db.Execute _
"Insert into tblDetailGearList (ProgramID, EquipRecordID) " &
_
"Values (" & lProgramID & ", " & _
lstSelectItems.ItemData(vItem) & ")", _
dbFailOnError

When I hover the mouse over this section I can see the correct
ProgramID value
"Values (" & lProgramID & ", " & _

When this section is highlighted in yellow I can see the following
values
lstSelectItems.ItemData(vItem) & ")", _
dbFailOnError
When I hover the cursor over lstSelectItems.ItemData9vItem) it says:
lstSelectItems.ItemData(vItem)=EquipmentRecordID

but hovering over (vItem)=Empty

I know this means that it is not picking up the EquipmentRecordID's of
the selected items but I don't know why
Click to expand...

I had a look at my earlier code and realised I had left out the
following lines

For Each vItem In lstSelectItems.ItemsSelected
(db exexcute section goes in here)
Next vItem

Which are supposed to cycle through the records in the list box. When
I add them back into the code I still get the 91 error but I am
actually getting a value for the EquipmentRecordID which corresponds
to the first item selected in the list box and the value of (vItem)=1

If all of the values I need can be seen from within the code why
doesn't it pass?

Darryn
 
(comments interspersed)

On Mon, 3 Nov 2003 13:50:57 -0500, "Dirk Goldgar"

Hi Dirk & Marshall
Thanks for your help so far,

Yes that was the aim, I also put a control on the form with this value
(hidden) as the user does not need to see it
Click to expand...

So that's fixed, then?
I did this in the declarations section.
Option Compare Database
Dim lProgramID As Variant
Option Explicit
Click to expand...

The Dim statement should really go after the Option Explicit statement,
but this should work. And you removed the "Dim lProgramID" statement
from the Form_Load() event procedure?
I am not really sure what I need to be passing that is my problem,
another person gave me some suggestions and snippets of code which I
have been trying to get working. I need to get the ProgramID number
from the main form into the subform with each item selected in the
multi select list box.
Click to expand...

Did you change the statement as I recommended?
Can you suggest a good book to get which explains all of the hows and
whys of this stuff, I am using A97 but will be u/grading to A2003 soon
Click to expand...

I've heard several books recommended frequently, but I can't remember
them offhand. I suggest you use Google Groups
(http://groups.google.com) to search the microsoft.public.access.*
newsgroups for "Access VBA books" -- or some such keywords as that --
and see what turns up. The _Access <version> Developer's Handbook_,
which will doubtless be among the books you'll come across, is a
terrific book but is probably too advanced for you at the moment. I
seem to recall a book by Smith & Sussman among the frequent
recommendations.
So far when I hit the compile button it seems ok
When I open the form and hit the open multiselect it opens
I then select a few items and hit the add Itesm button I get an error
91 object variable not set and the following code is highlighted

db.Execute _
"Insert into tblDetailGearList (ProgramID, EquipRecordID) " &
_
"Values (" & lProgramID & ", " & _
lstSelectItems.ItemData(vItem) & ")", _
dbFailOnError

When I hover the mouse over this section I can see the correct
ProgramID value
"Values (" & lProgramID & ", " & _

When this section is highlighted in yellow I can see the following
values
lstSelectItems.ItemData(vItem) & ")", _
dbFailOnError
When I hover the cursor over lstSelectItems.ItemData9vItem) it says:
lstSelectItems.ItemData(vItem)=EquipmentRecordID
Click to expand...

I don't quite follow here. It doesn't say the value of the list item is
the literal string "EquipmentRecordID", does it? That would be strange.
but hovering over (vItem)=Empty

I know this means that it is not picking up the EquipmentRecordID's of
the selected items but I don't know why
Click to expand...

I gather from your followup message that this last problem has been
solved by restoring the loop code surrounding the statement. But you
say in that message that you are still getting the Object Not Set error.
Did you by any chance delete and not restore the line,

Set db = CurrentDb

which must be executed before the line,

For Each vItem In lstSelectItems.ItemsSelected

? Please post the complete code of the cmdAddItemsToList_Click() event
procedure as it now stands.
 
So that's fixed, then? Yes


The Dim statement should really go after the Option Explicit statement,
but this should work. And you removed the "Dim lProgramID" statement
from the Form_Load() event procedure?
Click to expand...
I tried adding it after the Option Explicit statement but every time I
hit return to add a new line it went into the
Private Sub cmdAddItemsTolist section
henc I made tick by putting it between the tow lines!
Did you change the statement as I recommended? Yes

The _Access <version> Developer's Handbook_,
which will doubtless be among the books you'll come across, is a
terrific book but is probably too advanced for you at the moment. I
seem to recall a book by Smith & Sussman among the frequent
recommendations.
Click to expand...
Ta I am looking on amazon now
I gather from your followup message that this last problem has been
solved by restoring the loop code surrounding the statement. But you
say in that message that you are still getting the Object Not Set error.
Did you by any chance delete and not restore the line,

Set db = CurrentDb

which must be executed before the line,

For Each vItem In lstSelectItems.ItemsSelected

? Please post the complete code of the cmdAddItemsToList_Click() event
procedure as it now stands.
Click to expand...
That was the problem I had deleted those lines

Here is the code now

Private Sub cmdAddItemsToList_Click()
Dim vItem As Variant, db As DAO.Database
Set db = CurrentDb
For Each vItem In lstSelectItems.ItemsSelected
db.Execute _
"Insert into tblDetailGearList (ProgramID, EquipmentRecordID)
" & _
"Values (" & lProgramID & ", " & _
lstSelectItems.ItemData(vItem) & ")", _
dbFailOnError
Next vItem

End Sub

Thanks so much for your patience and help.

Do you mind if I ask a design/prcedural question also
In my subform I have additional fields which show up to give the user
information on the item selected ie colour,size, description.

All of that information I can get from using Dlookup via the
EquipmentRecordID field in each line of the subform.

I guess now I have this code working I could also pass those values
from the multi select list box as the query it is based on displays
them.

Which is the better way to go?

Darryn
 
On Tue, 4 Nov 2003 10:30:43 -0500, "Dirk Goldgar"

I tried adding it after the Option Explicit statement but every time I
hit return to add a new line it went into the
Private Sub cmdAddItemsTolist section
henc I made tick by putting it between the tow lines!
Click to expand...

I believe you'll find that if you had just finished typing the line and
pressed Enter it would have been relocated to the Declarations section.
That was the problem I had deleted those lines

Here is the code now

Private Sub cmdAddItemsToList_Click()
Dim vItem As Variant, db As DAO.Database
Set db = CurrentDb
For Each vItem In lstSelectItems.ItemsSelected
db.Execute _
"Insert into tblDetailGearList (ProgramID, EquipmentRecordID)
" & _
"Values (" & lProgramID & ", " & _
lstSelectItems.ItemData(vItem) & ")", _
dbFailOnError
Next vItem

End Sub

Thanks so much for your patience and help.
Click to expand...

So it's working now, right? Great.
Do you mind if I ask a design/prcedural question also
In my subform I have additional fields which show up to give the user
information on the item selected ie colour,size, description.

All of that information I can get from using Dlookup via the
EquipmentRecordID field in each line of the subform.

I guess now I have this code working I could also pass those values
from the multi select list box as the query it is based on displays
them.

Which is the better way to go?
Click to expand...

I don't think I'd go either of those routes, though if I had to I'd go
with the DLookups. Instead -- if it is at all practical -- I'd modify
the recordsource of the subform so that it's based on a query that joins
the table that holds those fields to tblDetailGearList, joining on the
EquipRecordID field. If you do that, the fields from that table will be
available for you to put on the subform, with no code or fancy work on
your part. In all probability those fields will be editable, too, so if
you don't want the user to change them you'd best be sure to lock the
controls.
 
Back
Top