How to show all booking records on form

[2k7mmurtaza]

Option Compare Database

Private Sub btn_Search()


Dim strsearch As String
Dim strText As String
strText = Me.txtSearch.Value
strsearch = "SELECT * from tblBookings where ((MemberID like ""*" & strText & "*"") Or (BookingID Like ""*" & strText & "*""))"
Me.RecordSource = strsearch
End Sub

Private Sub txtSearch_AfterUpdate()
Call btn_Search
End Sub


Private Sub txtShowAll_Click()


Dim strsearch As String
strsearch = "SELECT * from tblBookings"
Me.RecordSource = strsearch


End Sub

I have done this for all user accounts and it works perfectly fine. Is there anything I need to change to get my bookings to come up. It only shows bookings where I can click on the next record on the navigation bars. I need it to show up in a list when I click 'show all' button

[OBP]

I assume that you are trying to do this on a Form?

[2k7mmurtaza]

Hi yes on a form. I have the membership side working but not the booking.

[OBP]

So first of all is the Bookings Form in "Continuous Forms" mode or "Data Sheet" mode?
This will display the records like an Excel Worksheet.
The next question is does the Query show all the records you need.
If the forms are set up correctly, ie as Master & Subform you do not need to filter the Query, the subform is filtered by the Master/Child Links.

[2k7mmurtaza]

It was actually on a single form but i changed it to continuous since the membership form is continuous too.

The query shows all the records however it doesn't show all of them in a list when I click 'show all' button. I have to use the record filters and click next to view the other records.

Could it because I am searching by member ID or Booking ID, the Dim strSearch should be an integer since we're typing numbers and not text?

[OBP]

Are you trying to find all the Bookings, or the bookings for one member?
I am not sure from your description how you are trying to display the data or how the data is structured.
Normally you would display the members as single form records and the bookings of that member as a subform.
Can you show a printscreen of your Relationships?
Or provide an explanation of what it is your database is designed to do?

[2k7mmurtaza]

Sorry I wasn't clear. I am trying to find all bookings. I have a search function which i can type in their booking and member Id to bring their details which works fine. But I have a 'show all' button which should display all bookings but it doesn't.

I have a similar functionality for membership page where i can search the membership and show all button displays all the memberships. It work's fine for the membership page but the show all button does not work for the bookings page

I copied the coding from the membership page to the booking page but SQL to the booking table


databasebooking.JPG

This the database relationship

[OBP]

Have you checked to see if any filters are set on the form and also after the show all button is clicked what recordsource do you see in design mode?
Is it the same as the strSearch?

[2k7mmurtaza]

I don't think any filters are set on. This is the recordSource i get after clicking the show all button in design mode - SELECT tblBookings.BookingID, tblBookings.MemberID, tblBookings.Classes, tblBookings.Day FROM tblBookings;

strsearch = "SELECT * from tblBookings" - This is the strSearch so it is quite different

Is there a way we can work this out together?

[OBP]

Actually it is probably OK as there are no parameters in that record source either.
So at the moment I am stumped as to why it doesn't show them all.
Can you attach a zipped copy of the database with no personal data in it for me to look at?

[2k7mmurtaza]

Hi, Sure I can do that. What is the best way i can send you a zipped file? Really appreciate your help it means a lot

[OBP]

You should be able to add the file as an Attachment using the "Go Advanced" and then "Manage Attachments".

However there is a size limit, so it is best to Compact & Repair the database prior to Zipping it.

[2k7mmurtaza]

I have compressed the file but it still seems to have an issue exceeding the size limit. I am using Access 2016, so wanted to know if it is compatible with what your using? Because I tried sending the file to someone who uses Access 2013 and they couldn't seem to open it

[OBP]

OK.
You are correct I may well have trouble opening the database as I am still using Access 2007.
If you can create a 2007 formatted database and then import the tables and forms I will probably be able to work with it.
It may not import the tables if you have used the latest features that 2010/2012/2013 & 2016 have added.
We may have to find another way around your problem.