Listbox rowsoure problem: part 2!

  • Thread starter Thread starter Gavpav
  • Start date Start date
G

Gavpav

Hi, further to a message a few days ago:
hi, i have a form that is not attached to any data, on it
is a listbox. I need to be able to change the rowsource
property of this listbox when i open the form depending on
a value in a field on another form.

I am not too sure of VB, and have tried to do this using
macro's but to no avail. Any help would be much
appreciated.
Click to expand...

I have worked on the sugesstions mentioned, and found that
probably the only method is by VBA, i have tried my hand
at this, and managed to come up with this...

Public Sub openemail_Click()

If Forms![group-members]![id-tag] = "1" Then
Forms![frmEmailReport]![1stMailTo].RowSource = "SELECT
DISTINCTROW [mancom-email]., [mancom-email].[Second
Name], [mancom-email].[First Name] FROM [mancom-email];"
ElseIf Forms![group-members]![id-tag] = "2" Then
Forms![frmEmailReport]![1stMailTo].RowSource = "SELECT
DISTINCTROW [gencom-email].[Email], [gencom-email].[Second
Name], [gencom-email].[First Name] FROM [gencom-email];"
ElseIf Forms![group-members]![id-tag] = "3" Then
Forms![frmEmailReport]![1stMailTo].RowSource = "SELECT
DISTINCTROW [clubsec-email].[Email], [clubsec-email].
[Second Name], [clubsec-email].[First Name] FROM [clubsec-
email];"
ElseIf Forms![group-members]![id-tag] = "4" Then
Forms![frmEmailReport]![1stMailTo].RowSource = "SELECT
DISTINCTROW [clubrep-email].[Email], [clubrep-email].
[Second Name], [clubrep-email].[First Name] FROM [clubrep-
email];"
ElseIf Forms![group-members]![id-tag] = "5" Then
Forms![frmEmailReport]![1stMailTo].RowSource = "SELECT
DISTINCTROW [pastpres-email].[Email], [pastpres-email].
[Second Name], [pastpres-email].[First Name] FROM
[pastpres-email];"
ElseIf Forms![group-members]![id-tag] = "5" Then
Forms![frmEmailReport]![1stMailTo].RowSource = "SELECT
DISTINCTROW [fullsend-email].[Email], [fullsend-email].
[Second Name], [fullsend-email].[First Name] FROM
[fullsend-email];"
End If

End Sub


i dont have a clue if it is correct or not, but i cant
seem to get it to work, i would be very grateful if
someone could check it over...

Thanks Gavin
 
Hi,

You've got the general jist of this. I would recommend
using a select case instead of an if statement (you would
run into errors on your if statement because you would
need about 5 other end-ifs). This will make it much
easier to read. Try the following:


Public Sub openemail_Click()

Dim value As String
Dim sql As String
Dim frmGetting As Object
Dim frmReceiving as Object
' instantiate 2 form objects
Set frmGetting = Forms![group-members]
Set frmReceiving = Forms![frmEmailReport]

value = frmGetting.id-tag.Value ' the value you want to
inspect

Select Case value

Case Is = "1"
sql ="SELECT DISTINCTROW [mancom-email].,
[mancom-email].[Second
Name], [mancom-email].[First Name] FROM [mancom-email];"
Case Is = "2"
sql = "SELECT
DISTINCTROW [gencom-email].[Email], [gencom-email].
[Second
Name], [gencom-email].[First Name] FROM [gencom-email];"
Case Is = "3"
sql = "SELECT
DISTINCTROW [clubsec-email].[Email], [clubsec-email].
[Second Name], [clubsec-email].[First Name] FROM [clubsec-
email];"
Case Is = "4"
sql = "SELECT
DISTINCTROW [clubrep-email].[Email], [clubrep-email].
[Second Name], [clubrep-email].[First Name] FROM [clubrep-
email];"
Case Is = "5"
sql = "SELECT
DISTINCTROW [pastpres-email].[Email], [pastpres-email].
[Second Name], [pastpres-email].[First Name] FROM
[pastpres-email];"
Case Is = "6"
sql = "SELECT
DISTINCTROW [fullsend-email].[Email], [fullsend-email].
[Second Name], [fullsend-email].[First Name] FROM
[fullsend-email];"
Case Else
Msgbox "No such value exists"
End Select

'set the rowsource property once based on the sql
statement

frmReceiving.1stMailTo.RowSource = sql


End Sub
[QUOTE]
-----Original Message-----
Hi, further to a message a few days ago:
[QUOTE]
hi, i have a form that is not attached to any data, on it
is a listbox. I need to be able to change the rowsource
property of this listbox when i open the form[/QUOTE] depending[QUOTE]
on
a value in a field on another form.

I am not too sure of VB, and have tried to do this using
macro's but to no avail. Any help would be much
appreciated.[/QUOTE]

I have worked on the sugesstions mentioned, and found that
probably the only method is by VBA, i have tried my hand
at this, and managed to come up with this...

Public Sub openemail_Click()

If Forms![group-members]![id-tag] = "1" Then
Forms![frmEmailReport]![1stMailTo].RowSource = "SELECT
DISTINCTROW [mancom-email].[Email], [mancom-email]. [Second
Name], [mancom-email].[First Name] FROM [mancom-email];"
ElseIf Forms![group-members]![id-tag] = "2" Then
Forms![frmEmailReport]![1stMailTo].RowSource = "SELECT
DISTINCTROW [gencom-email].[Email], [gencom-email]. [Second
Name], [gencom-email].[First Name] FROM [gencom-email];"
ElseIf Forms![group-members]![id-tag] = "3" Then
Forms![frmEmailReport]![1stMailTo].RowSource = "SELECT
DISTINCTROW [clubsec-email].[Email], [clubsec-email].
[Second Name], [clubsec-email].[First Name] FROM [clubsec-
email];"
ElseIf Forms![group-members]![id-tag] = "4" Then
Forms![frmEmailReport]![1stMailTo].RowSource = "SELECT
DISTINCTROW [clubrep-email].[Email], [clubrep-email].
[Second Name], [clubrep-email].[First Name] FROM [clubrep-
email];"
ElseIf Forms![group-members]![id-tag] = "5" Then
Forms![frmEmailReport]![1stMailTo].RowSource = "SELECT
DISTINCTROW [pastpres-email].[Email], [pastpres-email].
[Second Name], [pastpres-email].[First Name] FROM
[pastpres-email];"
ElseIf Forms![group-members]![id-tag] = "5" Then
Forms![frmEmailReport]![1stMailTo].RowSource = "SELECT
DISTINCTROW [fullsend-email].[Email], [fullsend-email].
[Second Name], [fullsend-email].[First Name] FROM
[fullsend-email];"
End If

End Sub


i dont have a clue if it is correct or not, but i cant
seem to get it to work, i would be very grateful if
someone could check it over...

Thanks Gavin


.
[/QUOTE]
 
You said you can't get it to work. does it compile?
i assume you mean the data is not getting into your
listbox, in which case there's a good chance you need to
call ListBox.Requery once you change the value of RowSource

personally, i always use a callback function for my
ListBoxes
 
Thanks for your help, i have used the code you gave me and
it works like a treat...

Thanks again, Gavin






-----Original Message-----
Hi, further to a message a few days ago:
hi, i have a form that is not attached to any data, on it
is a listbox. I need to be able to change the rowsource
property of this listbox when i open the form depending on
a value in a field on another form.

I am not too sure of VB, and have tried to do this using
macro's but to no avail. Any help would be much
appreciated.
Click to expand...

I have worked on the sugesstions mentioned, and found that
probably the only method is by VBA, i have tried my hand
at this, and managed to come up with this...

Public Sub openemail_Click()

If Forms![group-members]![id-tag] = "1" Then
Forms![frmEmailReport]![1stMailTo].RowSource = "SELECT
DISTINCTROW [mancom-email]., [mancom-email]. [Second
Name], [mancom-email].[First Name] FROM [mancom-email];"
ElseIf Forms![group-members]![id-tag] = "2" Then
Forms![frmEmailReport]![1stMailTo].RowSource = "SELECT
DISTINCTROW [gencom-email].[Email], [gencom-email]. [Second
Name], [gencom-email].[First Name] FROM [gencom-email];"
ElseIf Forms![group-members]![id-tag] = "3" Then
Forms![frmEmailReport]![1stMailTo].RowSource = "SELECT
DISTINCTROW [clubsec-email].[Email], [clubsec-email].
[Second Name], [clubsec-email].[First Name] FROM [clubsec-
email];"
ElseIf Forms![group-members]![id-tag] = "4" Then
Forms![frmEmailReport]![1stMailTo].RowSource = "SELECT
DISTINCTROW [clubrep-email].[Email], [clubrep-email].
[Second Name], [clubrep-email].[First Name] FROM [clubrep-
email];"
ElseIf Forms![group-members]![id-tag] = "5" Then
Forms![frmEmailReport]![1stMailTo].RowSource = "SELECT
DISTINCTROW [pastpres-email].[Email], [pastpres-email].
[Second Name], [pastpres-email].[First Name] FROM
[pastpres-email];"
ElseIf Forms![group-members]![id-tag] = "5" Then
Forms![frmEmailReport]![1stMailTo].RowSource = "SELECT
DISTINCTROW [fullsend-email].[Email], [fullsend-email].
[Second Name], [fullsend-email].[First Name] FROM
[fullsend-email];"
End If

End Sub


i dont have a clue if it is correct or not, but i cant
seem to get it to work, i would be very grateful if
someone could check it over...

Thanks Gavin


.
[/QUOTE]
Click to expand...
 
Back
Top