List Box or Combo Box

[stang289hp]

Please help -
I have a list box (unbound) on a form that is populated by a Table/
Query search (set up through the wizard when the box was made). What I
would like to do is click on an item in that list box and have it's
record displayed in a seperate form for editing, (the initial form
with the list box will close). Can this be done ?

 

[stang289hp]

I must correct myself....

Please help -
I have a list box (unbound) that is populated by a Table/
Query search (set up through the wizard when the box was made), on a
form. What I
would like to do is click on an item in that list box and have it's
record displayed in a seperate form for editing, (the initial form
with the list box will close). Can this be done ?

 

[Vsn]

This can be done..........

table tblContacts = fID (autonumber / index
key),fName,fAddress,fWhatever,................

frm_1 select
frm_2 edit

frm_1
add one lstBox, than in the properties of this lstBox edit the following:
[Format] Column Count = 2, Column Widths = 0; (note the semicolon) (the
0 lenght hides the fID field bound)
[Data] Row Source = tblContacts, Bound Column = 1
[Event] On Dbl Click = [Event Procedure] (paste below code in VBA
editor)


'Private Sub lstContacts_DblClick(Cancel As Integer)
Dim stgFilter As String, stgWhere As String

stgWhere = "[fID]=" & Me.lstContacts
DoCmd.OpenForm "frm_2", acNormal, stgFilter, stgWhere, acFormEdit,
acWindowNormal
DoCmd.Close acForm, Me.Name, acSavePrompt
'End Sub

frm_2
in the properties of the form itself edit
[Data] Record Source = tblContacts
[Event] On Close = [Event Procedure] (paste below code in VBA editor)

'Private Sub Form_Close()
DoCmd.OpenForm "frm_1", acNormal, , , acFormEdit, acWindowNormal
'End Sub


Now add all the required data field on the form (any from your table
tblContacts).

SAVE ALL

Now if you open form frm_1, select a contact and double click on the lstbox,
frm_2 should open and frm_1 closes. Once you close frm_2, frm_1 opens again.

This is all I can think of now, hope this does the trick for you.

Success,
Ludovic

 

[stang289hp]

This can be done..........

table tblContacts = fID (autonumber / index
key),fName,fAddress,fWhatever,................

frm_1 select
frm_2 edit

frm_1
add one lstBox, than in the properties of this lstBox edit the following:
[Format] Column Count = 2, Column Widths = 0; (note the semicolon) (the
0 lenght hides the fID field bound)
[Data] Row Source = tblContacts, Bound Column = 1
[Event] On Dbl Click = [Event Procedure] (paste below code in VBA
editor)

'Private Sub lstContacts_DblClick(Cancel As Integer)
Dim stgFilter As String, stgWhere As String

stgWhere = "[fID]=" & Me.lstContacts
DoCmd.OpenForm "frm_2", acNormal, stgFilter, stgWhere, acFormEdit,
acWindowNormal
DoCmd.Close acForm, Me.Name, acSavePrompt
'End Sub

frm_2
in the properties of the form itself edit
[Data] Record Source = tblContacts
[Event] On Close = [Event Procedure] (paste below code in VBA editor)

'Private Sub Form_Close()
DoCmd.OpenForm "frm_1", acNormal, , , acFormEdit, acWindowNormal
'End Sub

Now add all the required data field on the form (any from your table
tblContacts).

SAVE ALL

Now if you open form frm_1, select a contact and double click on the lstbox,
frm_2 should open and frm_1 closes. Once you close frm_2, frm_1 opens again.

This is all I can think of now, hope this does the trick for you.

Success,
Ludovic

This probably matters but I'm using a linked data table. I keep
getting an error at the "Me.lstContacts" command. Any help with this ?

 

[Vsn]

Sorry, I do not see that it would make a difference for below code if your
tables were linked..... You might oversee a typo?

Ludovic

This can be done..........

table tblContacts = fID (autonumber / index
key),fName,fAddress,fWhatever,................

frm_1 select
frm_2 edit

frm_1
add one lstBox, than in the properties of this lstBox edit the following:
[Format] Column Count = 2, Column Widths = 0; (note the semicolon)
(the
0 lenght hides the fID field bound)
[Data] Row Source = tblContacts, Bound Column = 1
[Event] On Dbl Click = [Event Procedure] (paste below code in VBA
editor)

'Private Sub lstContacts_DblClick(Cancel As Integer)
Dim stgFilter As String, stgWhere As String

stgWhere = "[fID]=" & Me.lstContacts
DoCmd.OpenForm "frm_2", acNormal, stgFilter, stgWhere, acFormEdit,
acWindowNormal
DoCmd.Close acForm, Me.Name, acSavePrompt
'End Sub

frm_2
in the properties of the form itself edit
[Data] Record Source = tblContacts
[Event] On Close = [Event Procedure] (paste below code in VBA editor)

'Private Sub Form_Close()
DoCmd.OpenForm "frm_1", acNormal, , , acFormEdit, acWindowNormal
'End Sub

Now add all the required data field on the form (any from your table
tblContacts).

SAVE ALL

Now if you open form frm_1, select a contact and double click on the
lstbox,
frm_2 should open and frm_1 closes. Once you close frm_2, frm_1 opens
again.

This is all I can think of now, hope this does the trick for you.

Success,
Ludovic

This probably matters but I'm using a linked data table. I keep
getting an error at the "Me.lstContacts" command. Any help with this ?