Expert Required Please

[Andy G]

Hi,

I have 2 comboboxes as shown below on a seperate form called
frmSearchMailingList and a table called tblMailingList

The frmSearchMailingList is called from the main form frmEntryForm

What I need to achieve is a command button on frmEntryForm called 'search'
opens up frmSearchMailingList

frmSearchMailingList has the 2 comboboxes and a 'select' and 'cancel' cmd
button


cboFindWhat chooses the field, then cboSelectValue should list the relevant
data showing values for the field chosen in cboFindWhat
Then when the required record is highlighted, the 'Select' command button on
frmSearchMailingList will return all the details of that record on
frmEntryForm

The comboboxes are setup as shown below:


cboFindWhat
Row Source Type = Field List
Row Value = tblMailingList


cboSelectValue
Not yet setup as this is to list values depending on the option chosen with
the cboFindWhat combobox.

Could anybody please help with this

Many Thanks

Andy G

 

[Keith Wilby]

Hi,

I have 2 comboboxes as shown below on a seperate form called
frmSearchMailingList and a table called tblMailingList

The frmSearchMailingList is called from the main form frmEntryForm

What I need to achieve is a command button on frmEntryForm called 'search'
opens up frmSearchMailingList

frmSearchMailingList has the 2 comboboxes and a 'select' and 'cancel' cmd
button


cboFindWhat chooses the field, then cboSelectValue should list the
relevant data showing values for the field chosen in cboFindWhat
Then when the required record is highlighted, the 'Select' command button
on frmSearchMailingList will return all the details of that record on
frmEntryForm

The comboboxes are setup as shown below:


cboFindWhat
Row Source Type = Field List
Row Value = tblMailingList


cboSelectValue
Not yet setup as this is to list values depending on the option chosen
with the cboFindWhat combobox.

Could anybody please help with this

If I'm understanding you correctly then cboFindWhat will contain the name of
a field, so you could set up the source for cboSelectValue in cboFindWhat's
after update event:

Me.cboSelectValue.RowSource = "Select Distinct [" & Me.cboFindWhat & "] from
tblMailingList;"

You could also add code to ignore nulls and to sort it, etc.

I once did something along these lines and my "search" button called a
function to return a filter string and then opened a form with this filter:

Private Sub cmdSearch_Click()

Dim strFilterString As String
strFilterString = libComboFilter(Me)
DoCmd.OpenForm "Form2", , , strFilterString

End Sub

Public Function libComboFilter(frmForm As Form) As String

Dim intType As Integer, strField As String
Dim ctl As Control

For Each ctl In frmForm.Controls
If Not ctl.Name Like "cb*" Then GoTo Skip 'Only test combos
If IsNull(ctl.Value) Then GoTo Skip 'Ignore nulls

If ctl.Name Like "cbf*" Then 'this is a field name
strField = ctl.Value 'Get the field name
intType = frmForm.RecordsetClone.Fields(strField).Properties("Type")
'Get the data type
libComboFilter = libComboFilter & "[" & strField & "] = " 'Add the
field name to the filter
Else
If ctl.Name Like "cbo*" Then 'this is a value - test for data type

'Type 4 is Long, 7 is Double
If intType = 4 Or intType = 7 Then libComboFilter =
libComboFilter & ctl.Value & " or "

'Type 8 is Date
If intType = 8 Then libComboFilter = libComboFilter & "#" &
Format(ctl.Value, "mm/dd/yy") & "#" & " or "

'Type 10 is Text
If intType = 10 Then libComboFilter = libComboFilter & "'" &
ctl.Value & "'" & " or "

End If
End If
Skip:
Next

'Trim leading and trailing " and "
If Left(libComboFilter, 4) = " or " Then libComboFilter =
Right(libComboFilter, Len(libComboFilter) - 4)
If Right(libComboFilter, 4) = " or " Then libComboFilter =
Left(libComboFilter, Len(libComboFilter) - 4)

There's probably a more elegant way to code this but I did this against the
clock.

HTH - Keith.
www.keithwilby.com

 

[Guest]

You are actually over complicating this. A better and easier approach is to
put the the combos from frmSearchMailingList in either the form header of
form footer section of frmEntryForm. Make the sections visible property No.
Then in the detail section of frmEntryForm where the data is to be returned,
put a command button that makes the hidden section visible and sets the focus
to the first combo. Once you have made your selections and are ready to do
the entry, set the focus to the first control in the detail section, and make
the search section invisible again.

Another approach would be to make frmSearchMailingList your main form and
frmEntryForm a sub form.

 

[Guest]

Hi Keith,

Me.cboSelectValue.RowSource = "Select Distinct [" & Me.cboFindWhat & "] from
tblMailingList;" shows the data as I want, but when i try to select the
record in cboSelectValue, an error appears stating:
The value you entered isn't valid for this field
for example, you may have entered test in a numeric field or a number that
is larger thanthe field size permits

This error does not appear when selecting ID records in cboSelectValue
(which is numeric)
It seems to think that the value in cboSelectValue should be numeric,
however i am using fields such as ID (numeric), Last Name (text) Firstname
(text) etc....

Any ideas?

Thanks

Andy

Hi,

I have 2 comboboxes as shown below on a seperate form called
frmSearchMailingList and a table called tblMailingList

The frmSearchMailingList is called from the main form frmEntryForm

What I need to achieve is a command button on frmEntryForm called
'search' opens up frmSearchMailingList

frmSearchMailingList has the 2 comboboxes and a 'select' and 'cancel' cmd
button


cboFindWhat chooses the field, then cboSelectValue should list the
relevant data showing values for the field chosen in cboFindWhat
Then when the required record is highlighted, the 'Select' command button
on frmSearchMailingList will return all the details of that record on
frmEntryForm

The comboboxes are setup as shown below:


cboFindWhat
Row Source Type = Field List
Row Value = tblMailingList


cboSelectValue
Not yet setup as this is to list values depending on the option chosen
with the cboFindWhat combobox.

Could anybody please help with this

If I'm understanding you correctly then cboFindWhat will contain the name
of a field, so you could set up the source for cboSelectValue in
cboFindWhat's after update event:

Me.cboSelectValue.RowSource = "Select Distinct [" & Me.cboFindWhat & "]
from tblMailingList;"

You could also add code to ignore nulls and to sort it, etc.

I once did something along these lines and my "search" button called a
function to return a filter string and then opened a form with this
filter:

Private Sub cmdSearch_Click()

Dim strFilterString As String
strFilterString = libComboFilter(Me)
DoCmd.OpenForm "Form2", , , strFilterString

End Sub

Public Function libComboFilter(frmForm As Form) As String

Dim intType As Integer, strField As String
Dim ctl As Control

For Each ctl In frmForm.Controls
If Not ctl.Name Like "cb*" Then GoTo Skip 'Only test combos
If IsNull(ctl.Value) Then GoTo Skip 'Ignore nulls

If ctl.Name Like "cbf*" Then 'this is a field name
strField = ctl.Value 'Get the field name
intType =
frmForm.RecordsetClone.Fields(strField).Properties("Type") 'Get the data
type
libComboFilter = libComboFilter & "[" & strField & "] = " 'Add the
field name to the filter
Else
If ctl.Name Like "cbo*" Then 'this is a value - test for data type

'Type 4 is Long, 7 is Double
If intType = 4 Or intType = 7 Then libComboFilter =
libComboFilter & ctl.Value & " or "

'Type 8 is Date
If intType = 8 Then libComboFilter = libComboFilter & "#" &
Format(ctl.Value, "mm/dd/yy") & "#" & " or "

'Type 10 is Text
If intType = 10 Then libComboFilter = libComboFilter & "'" &
ctl.Value & "'" & " or "

End If
End If
Skip:
Next

'Trim leading and trailing " and "
If Left(libComboFilter, 4) = " or " Then libComboFilter =
Right(libComboFilter, Len(libComboFilter) - 4)
If Right(libComboFilter, 4) = " or " Then libComboFilter =
Left(libComboFilter, Len(libComboFilter) - 4)

There's probably a more elegant way to code this but I did this against
the clock.

HTH - Keith.
www.keithwilby.com

 

[Andy G]

Sorry, worked it out now

Now all I need to do is

1) clear cboSelectValue when cboFindWhat is changed
2) Have a command button to select the record chosen in cboSelectValue

Any ideas?

Thanks

Andy G

Hi,

I have 2 comboboxes as shown below on a seperate form called
frmSearchMailingList and a table called tblMailingList

The frmSearchMailingList is called from the main form frmEntryForm

What I need to achieve is a command button on frmEntryForm called
'search' opens up frmSearchMailingList

frmSearchMailingList has the 2 comboboxes and a 'select' and 'cancel' cmd
button


cboFindWhat chooses the field, then cboSelectValue should list the
relevant data showing values for the field chosen in cboFindWhat
Then when the required record is highlighted, the 'Select' command button
on frmSearchMailingList will return all the details of that record on
frmEntryForm

The comboboxes are setup as shown below:


cboFindWhat
Row Source Type = Field List
Row Value = tblMailingList


cboSelectValue
Not yet setup as this is to list values depending on the option chosen
with the cboFindWhat combobox.

Could anybody please help with this

If I'm understanding you correctly then cboFindWhat will contain the name
of a field, so you could set up the source for cboSelectValue in
cboFindWhat's after update event:

Me.cboSelectValue.RowSource = "Select Distinct [" & Me.cboFindWhat & "]
from tblMailingList;"

You could also add code to ignore nulls and to sort it, etc.

I once did something along these lines and my "search" button called a
function to return a filter string and then opened a form with this
filter:

Private Sub cmdSearch_Click()

Dim strFilterString As String
strFilterString = libComboFilter(Me)
DoCmd.OpenForm "Form2", , , strFilterString

End Sub

Public Function libComboFilter(frmForm As Form) As String

Dim intType As Integer, strField As String
Dim ctl As Control

For Each ctl In frmForm.Controls
If Not ctl.Name Like "cb*" Then GoTo Skip 'Only test combos
If IsNull(ctl.Value) Then GoTo Skip 'Ignore nulls

If ctl.Name Like "cbf*" Then 'this is a field name
strField = ctl.Value 'Get the field name
intType =
frmForm.RecordsetClone.Fields(strField).Properties("Type") 'Get the data
type
libComboFilter = libComboFilter & "[" & strField & "] = " 'Add the
field name to the filter
Else
If ctl.Name Like "cbo*" Then 'this is a value - test for data type

'Type 4 is Long, 7 is Double
If intType = 4 Or intType = 7 Then libComboFilter =
libComboFilter & ctl.Value & " or "

'Type 8 is Date
If intType = 8 Then libComboFilter = libComboFilter & "#" &
Format(ctl.Value, "mm/dd/yy") & "#" & " or "

'Type 10 is Text
If intType = 10 Then libComboFilter = libComboFilter & "'" &
ctl.Value & "'" & " or "

End If
End If
Skip:
Next

'Trim leading and trailing " and "
If Left(libComboFilter, 4) = " or " Then libComboFilter =
Right(libComboFilter, Len(libComboFilter) - 4)
If Right(libComboFilter, 4) = " or " Then libComboFilter =
Left(libComboFilter, Len(libComboFilter) - 4)

There's probably a more elegant way to code this but I did this against
the clock.

HTH - Keith.
www.keithwilby.com

 

[Guest]

Andy,

Can you post how you got it resolved please, I have the same problem and
this is where I got stuck and I was hoping you would post your solution for
others to see as well, it would be appreciated.

Otherwise, all this help is one way and won't help people who go to that
point and had that problem which wasn't discussed.

thanks,

Sorry, worked it out now

Now all I need to do is

1) clear cboSelectValue when cboFindWhat is changed
2) Have a command button to select the record chosen in cboSelectValue

Any ideas?

Thanks

Andy G

Hi,

I have 2 comboboxes as shown below on a seperate form called
frmSearchMailingList and a table called tblMailingList

The frmSearchMailingList is called from the main form frmEntryForm

What I need to achieve is a command button on frmEntryForm called
'search' opens up frmSearchMailingList

frmSearchMailingList has the 2 comboboxes and a 'select' and 'cancel' cmd
button


cboFindWhat chooses the field, then cboSelectValue should list the
relevant data showing values for the field chosen in cboFindWhat
Then when the required record is highlighted, the 'Select' command button
on frmSearchMailingList will return all the details of that record on
frmEntryForm

The comboboxes are setup as shown below:


cboFindWhat
Row Source Type = Field List
Row Value = tblMailingList


cboSelectValue
Not yet setup as this is to list values depending on the option chosen
with the cboFindWhat combobox.

Could anybody please help with this

If I'm understanding you correctly then cboFindWhat will contain the name
of a field, so you could set up the source for cboSelectValue in
cboFindWhat's after update event:

Me.cboSelectValue.RowSource = "Select Distinct [" & Me.cboFindWhat & "]
from tblMailingList;"

You could also add code to ignore nulls and to sort it, etc.

I once did something along these lines and my "search" button called a
function to return a filter string and then opened a form with this
filter:

Private Sub cmdSearch_Click()

Dim strFilterString As String
strFilterString = libComboFilter(Me)
DoCmd.OpenForm "Form2", , , strFilterString

End Sub

Public Function libComboFilter(frmForm As Form) As String

Dim intType As Integer, strField As String
Dim ctl As Control

For Each ctl In frmForm.Controls
If Not ctl.Name Like "cb*" Then GoTo Skip 'Only test combos
If IsNull(ctl.Value) Then GoTo Skip 'Ignore nulls

If ctl.Name Like "cbf*" Then 'this is a field name
strField = ctl.Value 'Get the field name
intType =
frmForm.RecordsetClone.Fields(strField).Properties("Type") 'Get the data
type
libComboFilter = libComboFilter & "[" & strField & "] = " 'Add the
field name to the filter
Else
If ctl.Name Like "cbo*" Then 'this is a value - test for data type

'Type 4 is Long, 7 is Double
If intType = 4 Or intType = 7 Then libComboFilter =
libComboFilter & ctl.Value & " or "

'Type 8 is Date
If intType = 8 Then libComboFilter = libComboFilter & "#" &
Format(ctl.Value, "mm/dd/yy") & "#" & " or "

'Type 10 is Text
If intType = 10 Then libComboFilter = libComboFilter & "'" &
ctl.Value & "'" & " or "

End If
End If
Skip:
Next

'Trim leading and trailing " and "
If Left(libComboFilter, 4) = " or " Then libComboFilter =
Right(libComboFilter, Len(libComboFilter) - 4)
If Right(libComboFilter, 4) = " or " Then libComboFilter =
Left(libComboFilter, Len(libComboFilter) - 4)

There's probably a more elegant way to code this but I did this against
the clock.

HTH - Keith.
www.keithwilby.com