populating dropdown from query

[Shift838]

I have no idea how to do this but what I want to do is populate a combo drop down box from results of a report. I want it to run a report based on month (january, february, etc) it will pass the month selection to the report to filter an owners name. i want the owners name to be added to the drop down box. It also must check if the owners name is already as a value in the drop down box if so it will skip.

Does anyone have any code they can post that does something similar to this?

 

[hermanlaksko]

Use part of your reports recordset to populate this combo and use group by to filter out dublicates:

Me!Combo.rowsource="SELECT YrTbl.YrClientName FROM YrTbl
GROUP BY YrTbl.YrClientName
HAVING YrTbl.Month=>" & YrFitstMonth & " AND YrTbl.Month=<" & YrLastMonth


Herman
Say no to macros

 

[Shift838]

ok let me get this straight given the code above..

The code above will access the actual table or will run a report?

 

[Golom]

hermanlaksko's code will retrieve data from the table "yrTbl" and use that retrieved data to populate the combo.

[small]On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. (Charles Babbage)[/small]

 

[hermanlaksko]

So to speak, yes. But you will have to modify the code to your needs, as I do not have your database.

The code is collecting data from an imaginary table called YrTbl, the data retrived is the data in the imaginary field YrClientName and data is filtred by the field Month

Herman
Say no to macros

 

[Shift838]

When I tried your code it just populates the combo box with the actual select statement.

My table name is NSAPLOG and the field I want to populate the combo box with is TXTIDOWNER by filtering a month that is selected in a combo month box (cmbmonth)

So I did this:

Me!cmbowners = "SELECT NSAPLOG.initialdatephase FROM NSAPLOG GROUP BY NSAPLOG.txtidowner HAVING NSAPLOG.initialreviewphase = '" & cmbmonth & "'"

 

[hermanlaksko]

Well perfect - did this solve your problem?

Herman
Say no to macros

 

[Shift838]

no. it only populates the combo box with the select statement. does not appear to get anything.

 

[hermanlaksko]

Why not use the same statement that you use for your report, surely it must do the trick.
Let us see your recordset for the report and I see whay I can do.

Herman
Say no to macros

 

[Shift838]

My report runs as follows:

If Me.cmbdatephase.Value = "January" Or Me.cmbdatephase.Value = "February" Or Me.cmbdatephase.Value = "March" Or Me.cmbdatephase.Value = "April" Or Me.cmbdatephase.Value = "May" Or Me.cmbdatephase.Value = "June" Or Me.cmbdatephase.Value = "July" Or Me.cmbdatephase.Value = "August" Or Me.cmbdatephase.Value = "September" Or Me.cmbdatephase.Value = "October" Or Me.cmbdatephase.Value = "November" Or Me.cmbdatephase.Value = "December" Then

strfilter = "InitialReviewPhase = '" & Me.cmbdatephase & "'"

DoCmd.OpenReport "NSAPIDMM", acViewPreview, , strfilter

I would like to add the txtidowner to the combobox here.

 

[PHV]

Set the [!]RowSource[/!] of the combo ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[hermanlaksko]

Me!cmbowners = "SELECT NSAPLOG.initialdatephase FROM NSAPLOG GROUP BY NSAPLOG.txtidowner HAVING NSAPLOG.initialreviewphase = '" & Me.cmbdatephase & "'"


Herman
Say no to macros

 

[Shift838]

This command only populates the combo box with the SELECT statement. Does not populate the combo box with IDOwners.

Should I be approching this differently maybe:

strsql = "Select * FROM NSAPLOG WHERE initialdatephase='" & cmbdatephase.Value & "'"

Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset

Set conn = CurrentProject.Connection
Set rs = New ADODB.Recordset
rs.Open strsql, conn, adOpenKeyset, adLockOptimistic, adCmdTable

cmbowners.AddItem (strsql)

 

[PHV]

As I already suggested:
Me!cmbowners.RowSource = "SELECT ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[hermanlaksko]

Oh sry..... Me!cmbowners.rowsource = "SELECT NSAPLOG.initialdatephase FROM NSAPLOG GROUP BY NSAPLOG.txtidowner HAVING NSAPLOG.initialreviewphase = '" & Me.cmbdatephase & "'"


Herman
Say no to macros

 

[hermanlaksko]

I guess that you solved the problem.
One thing that bothers me is that once a combobox is populated with more than 8 records the list will not scroll according to user scroll until user has reached the button of the list.
I have a solution to this little problem here:
Dim N
Me!cmbowners = "SELECT NSAPLOG.initialdatephase FROM NSAPLOG GROUP BY NSAPLOG.txtidowner HAVING NSAPLOG.initialreviewphase = '" & Me.cmbdatephase & "'"

N = Me!cmbowners.ListCount

Herman
Say no to macros

 

[Shift838]

No I have tried what has been suggested and nothing populates the drop down, just the select statement appears.

let me see.

I want to loop through a table called: NSAPLOG to retrieve a table called txtidowner based on a dropdown that is selected called cmbmonth.(january, februay, etc) before it populates the dropdown vb code will look at the dropdown current values to make sure the txtidwoner value is not already there. So it will not allow duplicates, if it is it will continue, otherwise will add the txtidowner information it has found int he database

 

[Shift838]

I have this code and it works fine without the loop statement and adds what I need it to (the first record only of course). When I add the loop statement it adds nothing.

Dim db As Database
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String
Dim strVAR As String

If Me.cmbdatephase.Value = "January" Or Me.cmbdatephase.Value = "February" Or Me.cmbdatephase.Value = "March" Or Me.cmbdatephase.Value = "April" Or Me.cmbdatephase.Value = "May" Or Me.cmbdatephase.Value = "June" Or Me.cmbdatephase.Value = "July" Or Me.cmbdatephase.Value = "August" Or Me.cmbdatephase.Value = "September" Or Me.cmbdatephase.Value = "October" Or Me.cmbdatephase.Value = "November" Or Me.cmbdatephase.Value = "December" Then

Set cnn = CurrentProject.Connection
strSQL = "Select txtidowner FROM NSAPLOG WHERE initialreviewphase='" & cmbdatephase.Value & "'"

Set rs = cnn.Execute(strSQL)

rs.MoveFirst

Do While Not rs.EOF

If rs.Fields(0).Value <> Null Then
cmbowners.AddItem (rs.Fields(0).Value)
End If
rs.MoveNext

Loop
rs.Close


Set rs = Nothing
Set cnn = Nothing

Else
MsgBox "Please select a Month to review."
End If

 

[PHV]

Why not simply this ?
If Me.cmbdatephase.Value = "January" Or Me.cmbdatephase.Value = "February" Or Me.cmbdatephase.Value = "March" Or Me.cmbdatephase.Value = "April" Or Me.cmbdatephase.Value = "May" Or Me.cmbdatephase.Value = "June" Or Me.cmbdatephase.Value = "July" Or Me.cmbdatephase.Value = "August" Or Me.cmbdatephase.Value = "September" Or Me.cmbdatephase.Value = "October" Or Me.cmbdatephase.Value = "November" Or Me.cmbdatephase.Value = "December" Then
cmbowners.RowSource = "SELECT txtidowner FROM NSAPLOG WHERE initialreviewphase='" & cmbdatephase.Value & "'"
Else
MsgBox "Please select a Month to review."
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Shift838]

PHV,

This does not work, it populates the combo box with only the select statement. I need it populated with the txtidowner values.