Solved filter \ sort in continuous form

[Gasman]

in sql view its

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???

 

[TipsyWolf]

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:

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

says was not found , but i filter the same locations that i already have in my form list.

 

[TipsyWolf]

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

SELECT [Tlocations].[LocationID], [Tlocations].[Location] FROM Tlocations ORDER BY [LocationID];

record source of mainform itself named FmasterList is

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;

 

[June7]

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 & "'"

 

[TipsyWolf]

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

so now i have
combo box row source

SELECT [Tlocations].[LocationID], [Tlocations].[Location] FROM Tlocations ORDER BY [Location];

form record source

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;

 

[TipsyWolf]

(autonumber?)

yes, with primary key

 

[TipsyWolf]

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

 

[June7]

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.

 

[TipsyWolf]

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

SELECT LocationID, Location FROM TLocations ORDER BY Location;

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

 

[June7]

Then at this point, you need to provide db for analysis. Follow instructions at bottom of my post.

 

[TipsyWolf]

Then at this point, you need to provide db for analysis. Follow instructions at bottom of my post.

done. please have a look.

 

[mike60smart]

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.

 

[TipsyWolf]

The form that opens is set as a Split Form

hey @mike60smart, thank u very much for help
is there a way to make the same, but in continiuse form instead of slip one and put it the filter to form header ?

i switch it to continiuse form and it stops working

 

[mike60smart]

hey @mike60smart, thank u very much for help
is there a way to make the same, but in continiuse form instead of slip one and put it the filter to form header ?

i switch it to continiuse form and it stops working

Hi
Check this version

 

[TipsyWolf]

Hi
Check this version

thank u very much, @mike60smart

i had to remove this 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.

 

[mike60smart]

Glad to help

 

[Gasman]

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.

 

[TipsyWolf]

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

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 ?

 

[Gasman]

Get a good book.
I just bought this the other week, as at the time I only had 2007, but no doubt there would

be 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...&sprefix=access+2019,aps,107&ref=nb_sb_noss_1

 

[TipsyWolf]

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 !