vba syntax, opening up specific form upon dbl click

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, I am designing a search form at the moment and would like some guidance
with the syntax, if anyone can help me, I'd much appreciate it. I have a
list box populated with records, each one with a unique identifier, other
fields, and a "type of case" identifier, which is like a tag. There are only
four choices under "type of case", each being a number (ie 1, 2, 3, 4). Now
when I dbl click on one of these records i would like one of four forms to
open up, depending on the "type of case" tag that the particular record has.
How does one define this logic in VBA?

Thanks,

- Matt
 
Try this

Dim lngID as long, lngCase as long, varWhere as variant

lngID = me!list.Column (1) 'Specify which column holds your RecordID
lngCase = me!list.Column(4) 'Specify which column holds your CaseID

'Go to specific record

varWhere = "[ID] = " & lngID

'Open Form

Select case lngcase

case is = 1 'open form 1

docmd.openform "Form1",,,varWhere

case is = 2 'open form 2

docmd.openform "Form2",,,varWhere


case is = 3 'open form 3

docmd.openform "Form3",,,varWhere


case is = 4 'open form 4

docmd.openform "Form4",,,varWhere

end select
end sub
 
Matt,

I think your code will look something like this...

Select Case Me.YourListBox.Column(2)
Case 1
DoCmd.OpenForm "FirstForm"
Case 2
DoCmd.OpenForm "SecondForm"
Case 3
DoCmd.OpenForm "ThirdForm"
Case 4
DoCmd.OpenForm "FourthForm"
End Select
 
Hi Armin_A, thanks for your advice on the VBA syntax. I pasted this into the
event procedure and made the proper amendments below, although when executed
I get a "runtime error 438, the object doesnt support this property or
method" - with the debugger highlighting the third line of the code below.
I'm not entirely sure what this means, any help on this is again appreciated,

Regards,
-Matt


Private Sub List0_DblClick(Cancel As Integer)

Dim lngID As Long, lngCase As Long, varWhere As Variant

lngID = Me!List0.[Type of case] 'Specify which column holds your RecordID
lngCase = Me!List0.[Case ID] 'Specify which column holds your CaseID

'Go to specific record

varWhere = "[ID] = " & lngID

'Open Form

Select Case lngCase

Case Is = 1 'open form 1

DoCmd.OpenForm "frmviewTR1", , , varWhere

Case Is = 2 'open form 2

DoCmd.OpenForm "frmviewTR2", , , varWhere


Case Is = 3 'open form 3

DoCmd.OpenForm "frmviewTR", , , varWhere


Case Is = 4 'open form 4

DoCmd.OpenForm "frmviewTR4", , , varWhere

End Select
End Sub



Armin_A said:
Try this

Dim lngID as long, lngCase as long, varWhere as variant

lngID = me!list.Column (1) 'Specify which column holds your RecordID
lngCase = me!list.Column(4) 'Specify which column holds your CaseID

'Go to specific record

varWhere = "[ID] = " & lngID

'Open Form

Select case lngcase

case is = 1 'open form 1

docmd.openform "Form1",,,varWhere

case is = 2 'open form 2

docmd.openform "Form2",,,varWhere


case is = 3 'open form 3

docmd.openform "Form3",,,varWhere


case is = 4 'open form 4

docmd.openform "Form4",,,varWhere

end select
end sub
Dr Poot said:
Hi, I am designing a search form at the moment and would like some guidance
with the syntax, if anyone can help me, I'd much appreciate it. I have a
list box populated with records, each one with a unique identifier, other
fields, and a "type of case" identifier, which is like a tag. There are only
four choices under "type of case", each being a number (ie 1, 2, 3, 4). Now
when I dbl click on one of these records i would like one of four forms to
open up, depending on the "type of case" tag that the particular record has.
How does one define this logic in VBA?

Thanks,

- Matt
Click to expand...
Click to expand...
 
Matt,

Me!List0.[Type of case] is not valid syntax. You need to use the Column
property of the Listbox to specify the column in the Listbox's Row
Source that contains the data you are referring to. My suggestion in my
earlier reply, being...
Me.List0.Column(2)
.... is based on the assumption/guess of the 3rd column - the columns are
numbered starting at 0.
 
Hi steve, thanks very much for your advice, it's solved my problem. I didnt
know about that column property of the listbox,

kind regards,
- Matt

Steve Schapel said:
Matt,

Me!List0.[Type of case] is not valid syntax. You need to use the Column
property of the Listbox to specify the column in the Listbox's Row
Source that contains the data you are referring to. My suggestion in my
earlier reply, being...
Me.List0.Column(2)
.... is based on the assumption/guess of the 3rd column - the columns are
numbered starting at 0.

--
Steve Schapel, Microsoft Access MVP


Dr said:
Hi Armin_A, thanks for your advice on the VBA syntax. I pasted this into the
event procedure and made the proper amendments below, although when executed
I get a "runtime error 438, the object doesnt support this property or
method" - with the debugger highlighting the third line of the code below.
I'm not entirely sure what this means, any help on this is again appreciated,

Regards,
-Matt
Click to expand...
Click to expand...
 
Back
Top