Help with Combo box display problem

[mblack76]

I need some help please
I have a db that has three forms (1 frmSearch, 1 frmFindCountyInfo and 1 subfrmFindCountyInfo (embedded in the frmFindCountyInfo)
I also have 2 tables (States and CountyInfo with StateID and CountyID set as PK in each).
Now I am trying to design it in such a way that when someone double clicks a State from the Mainform (FrmSearch), it should bring the frmFindCountyInfo with the corresponding state info for that State and this works fine.
Now in the subfrmFindCountyInfo, I want the user to be able to pick from a combobox a county within the Counties from the state selected and displayed in the frmFindCountyInfo
Data Source for FrmSearch: States
Data Source for FrmFindCountyInfo: States
Data Source for SubrmFindCountyInfo: CountyInfo
I also have a one to many relationship defined between StateID in States table and StateID in CountyInfo Table (I dunno if I am using it right though)

I have a query that works in Datasheet view but when I run the form, it picks just one county and there are non on the droplist.
Any help will be very much appreciated. Here are my queries…
Query to get State and Corresponding Info (works Fine)

SELECT States.StateID, [State] & " " & [Alias] AS StateFullName, States.Alias
FROM States;

Query to get County and corresponding data (seem to work in datasheetview but pulls up just first alphabetical county in combo box on form)

SELECT CountyInfo.County, CountyInfo.ReleaseFee, CountyInfo.ReleaseComment, CountyInfo.AssignmentFee, CountyInfo.AssignmentComment, CountyInfo.AdditionalInfo, CountyInfo.LastUpdated
FROM CountyInfo INNER JOIN States ON CountyInfo.StateID = States.StateID
WHERE ((([Forms]![FrmFindCountyInfo]![State])=[CountyInfo]![State]))
ORDER BY CountyInfo.County;

Also when I run subfrmFindCountyInfo alone ( I have to input State) it has the drop down with all the counties.
I dunno whatelse to do. Any help willbe very much appreciated.

 

[llkhoutx]

Query to get County and corresponding data (seem to work in datasheetview but pulls up just first alphabetical county in combo box on form)

SELECT CountyInfo.County, CountyInfo.ReleaseFee, CountyInfo.ReleaseComment, CountyInfo.AssignmentFee, CountyInfo.AssignmentComment, CountyInfo.AdditionalInfo, CountyInfo.LastUpdated
FROM CountyInfo INNER JOIN States ON CountyInfo.StateID = States.StateID
WHERE ((([Forms]![FrmFindCountyInfo]![State])=[CountyInfo]![State]))
ORDER BY CountyInfo.County;

You're filtering incorrectly. (1) Your filter should be a control where the State is specified, probably on the main form. (2) The filter has to concantenated to the SQL string. The filter should look like

". . . WHERE CountryInfo.StateID =" & Forms!FrmFindCountyInfo!StateID & " ORDER BY CountyInfo.County"

Note that Forms!FrmFindCountyInfo!StateID is concantenated. It changes with the the form. The way you originally specified it doesn't specifiy the value, so you get the first one.

All those brackets and paraentheses aren't requried and make things more complex. Brackets are required when fields names contain spaces - a practice to be avoided. Parentheses are required when algebraicly required.

 

[mblack76]

llkhoutx,
Thank you for the reply. I really appreciate it. I am new to Access and just copied the query from the SQL Window (I did it in design view)
Anyway, I tried using the bold part you suggested and it gave me an error (Data type mismatch in criteria expression). I am still trying to figure it out.
Once again THANK YOU for all your help

 

[llkhoutx]

Use the QBE window to build the query and the Expression Builder (Build option) the criteria. Right click the criteria row under the CountryInfo.StateID column and select 'Build'; then select the Main form and StateID, etc.

You may be calling the SQL Windown the QBE (uqery by expression) window.

The error indicates the WHERE clause is mismatching data types.

What's displayed in the CountryInfo.StateID table column and in the Forms!frmFindCountyInfo!StateID field? They should both be integers, probably Long.

 

[mblack76]

Well I did as you said and Still having the same error. This is the final query copied from the SQL Window having built it as you suggested

SELECT CountyInfo.County, CountyInfo.StateID, CountyInfo.RFCountyInfo, CountyInfo.ReleaseFee, CountyInfo.ReleaseComment, CountyInfo.AssignmentFee, CountyInfo.AssignmentComment, CountyInfo.AdditionalInfo, CountyInfo.LastUpdated
FROM CountyInfo INNER JOIN States ON CountyInfo.StateID = States.StateID
WHERE (([CountyInfo]![StateID]="& Forms![FrmFindCountyInfo]![StateID]"))
ORDER BY CountyInfo.County;

Here are the other properties:
Tables:
States- StateID (Number, field size= Double), State(Text), Alias(Text), RFCodeState (Text), Additional Info (Tetx)
CountyInfo: CountyInfoID (AutoNumber), StateID (Number set to Double) then 6 other Columns all set to text)
Forms:
FrmCounty: TxtStateID (Control Source StateID)
FrmFrmFindCountyInfo: TxtStateID (Control Source StateID)
On main form, user double clicks in list box to select state

The query for state on FrmCounty is sState (Works Fine)

SELECT States.StateID, [State] & " " & [Alias] AS StateFullName, States.Alias
FROM States;

List box Event Procedure on Mainfrm (FrmCounty)

Private Sub List0_DblClick(Cancel As Integer)
On Error GoTo Err_List0_DblClick
Dim RS As Object
If IsNull(Me.List0) Then
MsgBox "Please select a State! ", vbCritical, "Required"
Me.List0.SetFocus
Exit Sub
Else
DoCmd.OpenForm "frmFindCountyInfo"
Set RS = Forms!frmFindCountyInfo.Recordset.Clone
RS.FindFirst "StateID = " & Me.List0
Forms!frmFindCountyInfo.Bookmark = RS.Bookmark
DoCmd.Close acForm, Me.Name
End If
Exit_List0_DblClick:
Exit Sub
Err_List0_DblClick:
Resume Exit_List0_DblClick
End Sub

And the listbox Row Source is

SELECT States.StateID, [State] & " " & [Alias] AS [State Name] FROM States WHERE (((States.State) Like "*" & Forms!frmCounty!txtSearch2 & "*")) Or (((States.Alias) Like "*" & Forms!frmCounty!txtSearch2 & "*")) ORDER BY States.State;

Like I said moving from the main form to the FrmFindCounty works fine.

 

[llkhoutx]

Evidently the state comes from the selected row in a MainForm list box. That listbox contain's two columns, StateID, State; the first is a hidden long integer and the last displayed text. Only the states are visible in the list box.

List boxes should not be double-clicked to select a row and start an action because your user will miss the correct row. The "Multi Select" property of the list box should be "None" without the quotation marks.

As I understand things the selected row in the listbox is the pseudo filter. I say pseudo because the StateID should be the filter. Therefore the selected row contains the StateID in column 0.

The StateID column of the selected row is your filter. Your're selecting the list box record is incorrectly, use

Dim db As DAO.Database
Dim varitem As Variant
Dim ncnt As Integer
Dim StateID As Long
ncnt = 0
With Me!lstListBoxName
For Each varitem In .ItemsSelected
ncnt = ncnt + 1
StateID = .Column(0, varitem) 'columns numbered 0,1,2,3,4...
GOTO SelectedStateID
Next
End
'trigger error if code fell through loop, nothing selected
SelectedStateID:
'store the select item in a textbox - this is the filter
forms!MainForm!aTextBox = StateID

"forms!MainForm!aTextBox" is the filter value.


Then, your still filtering worng with

WHERE (([CountyInfo]![StateID]="& Forms![FrmFindCountyInfo]![StateID]"))

THe type mismatch is being caused by

StateID (Number, field size= Double),

StateID should have a Long Integer datatype to match the datatype of the subform ID.

Of course, moving to the subform works fine, it's merely an openform. I'll bet that you listbox event doesn't otherwise postion to the correct bookmark, your not getting StateID.

SELECT CountyInfo.County, CountyInfo.StateID, CountyInfo.RFCountyInfo, CountyInfo.ReleaseFee, CountyInfo.ReleaseComment, CountyInfo.AssignmentFee, CountyInfo.AssignmentComment, CountyInfo.AdditionalInfo, CountyInfo.LastUpdated
FROM CountyInfo INNER JOIN States ON CountyInfo.StateID = States.StateID
WHERE (([CountyInfo]![StateID]="& Forms![FrmFindCountyInfo]![StateID]"))
ORDER BY CountyInfo.County;

should be

"SELECT CountyInfo.County, CountyInfo.StateID, CountyInfo.RFCountyInfo, CountyInfo.ReleaseFee, CountyInfo.ReleaseComment, CountyInfo.AssignmentFee, CountyInfo.AssignmentComment, CountyInfo.AdditionalInfo, CountyInfo.LastUpdated
FROM CountyInfo INNER JOIN States ON CountyInfo.StateID = States.StateID
WHERE (([CountyInfo]![StateID]="& Forms![FrmFindCountyInfo]![StateID] & "))
ORDER BY CountyInfo.County;"

 

[mblack76]

Guys I need help please,
I have this db that I have been trying to get to work and been unable to implement llkhoutx suggestions successfully. Its an existing database that I wanted to modify. This is the idea I have or want to implement. I want that when a user opens the db he/ she should get the frmCounty. Then when they select a state by typing and then double clicking they get the frmFindcountyInfo. On that form the Statewide comments should be displayed so as the StateVarious, State and Alias. Then the user can select a county from the droplist (that is loaded based on the state selected) in the subfrmFindCountyInfo. When a county is selected, the RFcode, Release fee, Assignment fee, and comments are displayed and they change based on the county selected.
I hope this makes it clearer. I would appreciate any help at making it work.

 

[llkhoutx]

I couldn't open your zip file.

 

[mblack76]

I couldn't open your zip file.

I took it down and now cant even upload another. I am so frustrated you have no idea. I will keep trying though.