Solved filter \ sort in continuous form (1 Viewer)

in sql view its

Code:
SELECT Tmain.RiskID, Tmain.RiskCode, Tmain.riskTitle, Tmain.isActive, Tmain.RiskdueDate, Tmain.RiskDate, Tmain.location, Tmain.locationPricies, Tmain.risktype, Tmain.riskcolor, Tmain.reporter, Tmain.picafter, Tmain.picbefore
FROM Tmain;

yes

short text
According to post #11 it is called LocationID ?, which to me would always be numeric?
Even that completely different sql is still ID???
 
It is a string variable that holds the filter statement.
So now you need to set the filter, then set the filter on.
Me.Filter = strFilter
Me.FilterOn = True
upd:
Code:
Private Sub cbolocation_AfterUpdate()
Dim strFilter As String

If Me.cbolocation <> "" Then
    strFilter = "location = " & Me.cbolocation
End If
Me.Filter = strFilter
Me.FilterOn = True
End Sub

@June7 suggest me to remove column(0). i removed it
1712604480646.png


says was not found , but i filter the same locations that i already have in my form list.
 
According to post #11 it is called LocationID ?, which to me would always be numeric?
Even that completely different sql is still ID???
it was row source of combo box named cbolocation
its
Code:
SELECT [Tlocations].[LocationID], [Tlocations].[Location] FROM Tlocations ORDER BY [LocationID];

record source of mainform itself named FmasterList is
Code:
SELECT Tmain.[RiskID], Tmain.RiskCode, Tmain.riskTitle, Tmain.isActive, Tmain.[RiskdueDate], Tmain.RiskDate, Tmain.location, Tmain.locationPricies, Tmain.risktype, Tmain.riskcolor, Tmain.reporter, Tmain.picafter, Tmain.picbefore FROM Tmain;
 
If Tmain.location is text type and Tlocations.LocationID is number (autonumber?), these fields are not compatible types and cannot filter Tmain.location with combobox value that is LocationID. Should be saving LocationID into Tmain.location which should be a number (long Integer) type.

If you really want to save location text and search on that (bad design), then LocationID field serves no purpose. Remove from combobox RowSource and don't designate it as primary key in Tlocations table. In fact, the field is then not even needed in table.

Using text parameters requires apostrophe delimiters.

"location = '" & Me.cbolocation & "'"
 
Last edited:
If Tmain.location is text type and Tlocations.LocationID is number (autonumber?), these fields are not compatible types and cannot filter Tmain.location with combobox value that is LocationID. Should be saving LocationID into Tmain.location which should be a number (long Integer) type.

If you really want to save location text and search on that (bad design), then LocationID field serves no purpose. Remove from combobox RowSource and don't designate it as primary key in Tlocations table. In fact, the field is then not even needed in table.

Using text parameters requires apostrophe delimiters.

"location = '" & Me.cbolocation & "'"
wierd.
i removed current combo box and created a new one where i dont even drag locationID anywhere
1712606002446.png


1712606017925.png
1712606042234.png



so now i have
combo box row source
Code:
SELECT [Tlocations].[LocationID], [Tlocations].[Location] FROM Tlocations ORDER BY [Location];

form record source
Code:
SELECT Tmain.[RiskID], Tmain.RiskCode, Tmain.riskTitle, Tmain.isActive, Tmain.[RiskdueDate], Tmain.RiskDate, Tmain.location, Tmain.locationPricies, Tmain.risktype, Tmain.riskcolor, Tmain.reporter, Tmain.picafter, Tmain.picbefore FROM Tmain;
 
these fields are not same type
that gets me even more wierd as i dont see here any IDs. its only data from Tlocation table from Location field.
in dropdown list there are site # 1 , 4 , 5 etc
1712606414082.png


1712606472907.png
 
Combobox is set to hide the LocationID. This is accomplished with ColumnWidths property. Any column with 0 width does not display. Combobox properties should be like:

RowSource: SELECT LocationID, Location FROM TLocations ORDER BY Location;
ColumnCount: 2
ColumnWidths: 0",1.0" (or in your case probably cm)
BoundColumn: 1

This means combobox value is LocationID even though location text is displayed. TMain.location should be number type and contain LocationID as foreign key - basic RDBMS concept.
 
Last edited:
Combobox is set to hide the LocationID. This is accomplished with ColumnWidths property. Any column with 0 width does not display. Combobox properties should be like:

RowSource: SELECT LocationID, Location FROM TLocations ORDER BY Location;
ColumnCount: 2
ColumnWidths: 0",1.0" (or in your case probably cm)
BoundColumn: 1

This means combobox value is LocationID and TMain.location should be number type and contain LocationID as foreign key. Basic RDBMS concept.
did the same as u suggest it

1712607636452.png


Code:
SELECT LocationID, Location FROM TLocations ORDER BY Location;

Code:
Option Compare Database
Option Explicit

Private Sub cbolocation_AfterUpdate()
Dim strFilter As String

If Me.cbolocation <> "" Then
    strFilter = "location = " & Me.cbolocation
End If
Me.Filter = strFilter
Me.FilterOn = True
End Sub

Private Sub Form_Load()
  DoCmd.Maximize
End Sub
 
Then at this point, you need to provide db for analysis. Follow instructions at bottom of my post.
 
done. please have a look.
Hi
This solution is based on your previous database.

The form that opens is set as a Split Form and there is an Unbound Textbox in the header.
Just type the data you want to filter by and hit Enter and it will filter for that data.
The Clear Command button returns all data.
 

Attachments

Hi
Check this version
thank u very much, @mike60smart

i had to remove this code

Code:
Private Sub Form_Load()

10        On Error GoTo Form_Load_Error
20    DoCmd.Maximize
         
30        On Error GoTo 0
40        Exit Sub

Form_Load_Error:

50        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Form_Load, line " & Erl & "."

End Sub

cause it links to Form_load_Error, which i dont have. i cant read code, but as i already starting understand here - its prob calls a text message in case of error.

i removed this part of code and everything works flawlessly for me.
 
What do you think Form_Load_Error: is? so you did have it and if an error managed to occur in that very small block of code, you would get the message.

All that code does is maximise the form anyway. :(

You really need to take a step back and start learning some code, and especially logic.
Doing this way the way you are doing it is going to be extremely painful and slow.

Bit like me trying brain surgery, I will get there in the end, but who will I hurt in the meantime. :)
 
What do you think Form_Load_Error: is? so you did have it and if an error managed to occur in that very small block of code, you would get the message.
the reason i had to remove it , cause it gave me an error
1712680969091.png
i he
Bit like me trying brain surgery, I will get there in the end, but who will I hurt in the meantime.
i hear ya :)
You really need to take a step back and start learning some code, and especially logic.
what could u suggest for me as a person who knows only excel formulas well ... : ) so i can code in sql and vba ?
 
Get a good book.
I just bought this the other week, as at the time I only had 2007, but no doubt there would View attachment 113610be plenty in there for later versions. Or try and find a later version of the same.
I plan to just read it as a novel. :)

Edit: Plenty here https://www.amazon.co.uk/s?k=access+2019&crid=16D7LXXF1Q6DU&sprefix=access+2019,aps,107&ref=nb_sb_noss_1
thank you for suggestion.
i'll try to find something for me next week.
i appriciated your help !
 

Users who are viewing this thread

Back
Top Bottom