Datasheet Form Not UpdatingAfter Search (1 Viewer)

vagues0ul · Sep 18, 2018

i have created a split form and added a textbox to search for a particular data on the datasheet. The button on which i have applied a macro to search for particular data is working fine but it generates a problem. When i search for a text it displays the data in datasheet view but after clearing the textbox the datasheet view does not display the full list of all the data.

here is code i added in the Macro Builder of the button

="Passport_Number = '" & [Forms]![Client_Details]![searchtxt] & "'"

kidnly tell how to display all the data back in datasheet of the split form after the search is complete. Thanks

CJ_London · Sep 18, 2018

I don't use macros so cannot give an exact solution but you need something that says

if searchtxt="" then turn off filter.

In vba the full code would be

Code:

if searchtxt="" then
    me.filteron=false
else
    me.filter="Passport_Number = '" & searchtxt & "'"
    me.filteron=true
end if

However I also don't use split forms (they have limitations) so this may not work

vagues0ul · Sep 18, 2018

tried your code and its not working. after updating the button with the vba code now even the data is not filtering when i click on search.

CJ_London · Sep 18, 2018

I've had to make a number of assumptions. it may be something to do with using a split form - they do have their limitations. Or perhaps you have not applied the code correctly. Post all the code you are using i.e. copy and paste it and use the code tags (highlight the pasted text and click the # button).

vagues0ul · Sep 21, 2018

sorry for late reply. i am attaching the database and there i have created two form and applied vba code on both of them to find a record with a combo box. but both of them are not filtering any data. kindly check them and let me know where i am missing something. thanks.

main form with combo search is = Client_Details
search form with combo is = serach-form

CJ_London · Sep 21, 2018

cboppt - your search combo rowsource includes the maintableID so you are trying to compare a passport text value to a id numeric value. Solution, remove the ID from your rowsource - and suggest use DISTINCT to remove duplicate passports

SELECT DISTINCT [Passport_Number] FROM [MainTable]

cboppt and tts - you are looking for a text value so you need to use text delimiters

ppt = "select * from MainTable where ([Passport_Number] = '" & Me.cboppt & "')"

JHB · Sep 21, 2018

Change it to the below:

Code:

Private Sub cboppt_AfterUpdate()
Dim ppt As String
ppt = "select * from MainTable where ([Passport_Number] = [B][COLOR=red]'[/COLOR][/B]" & Me.cboppt[B][COLOR=red].Column(1)[/COLOR][/B] & "[B][COLOR=Red]'[/COLOR][/B])"
Me.client_sub1.Form.RecordSource = ppt
Me.client_sub1.Form.Requery
End Sub

CJ_London · Sep 21, 2018

Also meant to say, no need to requery after assigning a new recordsource - it will requery automatically

vagues0ul · Sep 21, 2018

JHB said:

Change it to the below:

Code:

Private Sub cboppt_AfterUpdate()
Dim ppt As String
ppt = "select * from MainTable where ([Passport_Number] = [B][COLOR=red]'[/COLOR][/B]" & Me.cboppt[B][COLOR=red].Column(1)[/COLOR][/B] & "[B][COLOR=Red]'[/COLOR][/B])"
Me.client_sub1.Form.RecordSource = ppt
Me.client_sub1.Form.Requery
End Sub

now the list is showing nothing

vagues0ul · Sep 21, 2018

CJ_London said:
cboppt - your search combo rowsource includes the maintableID so you are trying to compare a passport text value to a id numeric value. Solution, remove the ID from your rowsource - and suggest use DISTINCT to remove duplicate passports

SELECT DISTINCT [Passport_Number] FROM [MainTable]

cboppt and tts - you are looking for a text value so you need to use text delimiters

ppt = "select * from MainTable where ([Passport_Number] = '" & Me.cboppt & "')"

it didnt work on both textbox or the combobox. the values are now showing. can you change it on the db and upload >

CJ_London · Sep 21, 2018

you also need to change the combo column count to 1

vagues0ul · Sep 21, 2018

CJ_London said:
you also need to change the combo column count to 1

i did but its still showing blanck

vagues0ul · Sep 21, 2018

CJ_London said:
you also need to change the combo column count to 1

you can clearly see in the picture attached with this reply

CJ_London · Sep 21, 2018

what about column widths - should be blank.

vagues0ul · Sep 21, 2018

CJ_London said:
what about column widths - should be blank.

can you update the db file with the suggestions you are providing so that it would be easy for me to look where i have done the mistake.

it will be so nice of you.

thanks in advance

JHB · Sep 21, 2018

Database attached, open form serach-form:

vagues0ul · Sep 21, 2018

list is populated but results are not filterig. no matter which value i select from combobox the datasheet remains the same.

vagues0ul · Sep 21, 2018

JHB said:
Database attached, open form serach-form:

is there something wrong with my access settings? i have also dowloaded a search form https://access-programmers.co.uk/forums/showthread.php?t=99353

but none of its search controlls are working too. :banghead::banghead:

JHB · Sep 21, 2018

I don't know, here it works, see picture, (and the same it should by you with the database I uploaded.).

vagues0ul · Sep 21, 2018

JHB said:
I don't know, here it works, see picture, (and the same it should by you with the database I uploaded.).

how dumb i am. the trust center was turned off and activex was disabled thus no code was running

thanks for the help

Datasheet Form Not UpdatingAfter Search (1 Viewer)

Registered User.

Super Moderator

Registered User.

Super Moderator

Registered User.

Attachments

Super Moderator

Have been here a while

Super Moderator

Registered User.

Attachments

Registered User.

Super Moderator

Registered User.

Registered User.

Attachments

Super Moderator

Registered User.

Have been here a while

Attachments

Registered User.

Registered User.

Have been here a while

Attachments

Registered User.

Similar threads

Users who are viewing this thread