Problems with code when using SP List (1 Viewer)

[DinkyDon]

Hi all, I've been trying to move my database into a public space so everyone in the company has access to the database, and after having some trouble getting certain elements to work, I decided to try building a new database front end around the pre-existing database now held in SP list. I thought that this would be a fairly easy approach, just build out the same code and voila, but this hasn't been the case. As far as I can tell, I've used the same code with no success.

On my Homepage form I have a new record button which works fine, but I also have a textbox/listbox search function which isn't working, and the associated edit record button isn't working either. The code I have in place for all of these is as follows:

ClientSearchBox_AfterUpdate
EditClientResults.Requery

EditClientResults_AfterUpdate
EditRecordButton.Enabled

EditRecordButton_OnClick
DoCmd.OpenForm "EditClientData" , , , "[ClientDatabase.Client]=" & "'" Me.EditClientResults.Column(0) & "'"

I am also using the same criteria in the search query that feeds into the form, which is as follows:

Like "*" & [Forms]![Homepage]![EditClientResults] & "*"


This code and query criteria has worked both before and after the database has been split. Splitting it did come up with some other problems, which is why I thought it would be simpler to build directly from the online data. Any help would be greatly appreciated in this!

 

[Minty]

If you are using the same criteria, you are effectively double filtering the results.

Try removing the filter from the query.

 

[DinkyDon]

If you are using the same criteria, you are effectively double filtering the results.

Try removing the filter from the query.

This hasn't had any effect. I think the problem lies with the requery VBA, as there is no change in the list box when I search something in the text box

 

[Minty]

What is EditClientResults ?
(If it's a Listbox I would call it lstClients by the way.)

 

[DinkyDon]

What is EditClientResults ?
(If it's a Listbox I would call it lstClients by the way.)

It is a listbox yeah. I know, quite a clunky naming scheme!

 

[Minty]

If the requery on the listbox isn't working, I would start there.
What is the rowsource for the listbox set to ?

 

[DinkyDon]

If the requery on the listbox isn't working, I would start there.
What is the rowsource for the listbox set to ?

SELECT [ClientDatabaseSearch].[ID], [ClientDatabaseSearch].[Client], [ClientDatabaseSearch].[Hardware], [ClientDatabaseSearch].[Software] FROM ClientDatabaseSearch ORDER BY [ID], [Client], [Hardware], [Software];

This is what the row source was as default. I tried changing it to both the query and the database table, and while the table row source showed results, it still didn't update after searching for a record. Should I set the row source to a specific field within the data table/query?

 

[Minty]

I'm really confused by your naming of objects - do you really have a table called ClientDatabaseSearch ? Or is that a query based on the Client table?

As it stands there is nothing in that rowsource that would be affected by the contents of a search box. A requery would do nothing.
Unless you have a lot of clients/records isn't the listbox suitable to search for a client. If you removed the [ID] from the order by clause field they would be sorted alphabetically by the client name?

Going back to your original issue - Column(0) would return the ID field but you are looking for a text value in this;
"[ClientDatabase.Client]=" & "'" Me.EditClientResults.Column(0) & "'"

So you would never get a match.

And change ID to Client_ID - then you know what ID you are looking for, if you add another table with another ID field you'll get confused.

 

[DinkyDon]

I'm really confused by your naming of objects - do you really have a table called ClientDatabaseSearch ? Or is that a query based on the Client table?

As it stands there is nothing in that rowsource that would be affected by the contents of a search box. A requery would do nothing.
Unless you have a lot of clients/records isn't the listbox suitable to search for a client. If you removed the [ID] from the order by clause field they would be sorted alphabetically by the client name?

Going back to your original issue - Column(0) would return the ID field but you are looking for a text value in this;
"[ClientDatabase.Client]=" & "'" Me.EditClientResults.Column(0) & "'"

So you would never get a match.

And change ID to Client_ID - then you know what ID you are looking for, if you add another table with another ID field you'll get confused.

The ClientDatabaseSearch is a Query based on the data table.

In the list box, the results I can see are already sorted alphabetically. However there is no change after searching for a specific record.

I've tried changing the column(0) to column(1), and this hasn't had any effect.

You mentioned that nothing would be affected by the contents of a search box, what then should I change the rowsource to to trigger the requery event? The client field in the data table?

 

[Minty]

Instead of an empty search box, why not have a combo with just the client names listed and use that to filter the listbox.

The combo rowsource would be something like

SELECT Distinct ClientID, ClientName From YourClientEquimentTable

Then your criteria for your listbox query would be

WHERE ClientID = Forms!Homepage!cboCLientList or Forms!Homepage!cboCLientList Is Null

I'm making some assumptions about your data structure e,g. you have your client ID in your equipment lists, not their name.

 

[DinkyDon]

W

Instead of an empty search box, why not have a combo with just the client names listed and use that to filter the listbox.

The combo rowsource would be something like

SELECT Distinct ClientID, ClientName From YourClientEquimentTable

Then your criteria for your listbox query would be

WHERE ClientID = Forms!Homepage!cboCLientList or Forms!Homepage!cboCLientList Is Null

I'm making some assumptions about your data structure e,g. you have your client ID in your equipment lists, not their name.

I've had a little play around with this, and this doesn't seem appropriate for me, as in my data table, I have multiple records for each client (owing to multiple pieces of equipment they own) and this method only shows me the client name, so when editing a record I can't differentiate between multiple instances of the same client. In addition, I have to click on the arrow to show the options, rather than them being shown straight away.

I've found a video where the fella has an option in the combo box wizard to "Find a record on my form based on the value I select in my combo box", who has all fields displayed, but I don't have this option, only to get values from a query/table, or to type in the values I want.

 

[Minty]

My suggestion was to use the combo to select the Client, and use the listbox to display only that client's hardware.
A "pre filter" as it were.

So you have 3000 pieces of equipment spread across 100 Clients.
The combo only lists the clients.

You select the client in the combo, it filters your list box to their 30 ish items of equipment.
You click on the equipment item, and it opens the form to edit it.

 

[DinkyDon]

My suggestion was to use the combo to select the Client, and use the listbox to display only that client's hardware.
A "pre filter" as it were.

So you have 3000 pieces of equipment spread across 100 Clients.
The combo only lists the clients.

You select the client in the combo, it filters your list box to their 30 ish items of equipment.
You click on the equipment item, and it opens the form to edit it.

How would this be achieved? I'm unsure that the failed requery problem would be sorted with the list box, as it would still need to requery following the selection in the combo box. Also, how would you make the combo box show only Client A one time? As it stands Client A's name appears multiple times, making it impossible to discern between records.

 

[Minty]

This does that

SELECT Distinct ClientID, ClientName From YourClientEquimentTable

To test it put it in a query and try it.
The DISTINCT keyword means bring back only unique records.
The added bonus is you will only see clients that actually have equipment if you do it this way.

If you post up a small sample database I can probably demonstrate, just enough anonymous data to demonstrate the principles.

 

[DinkyDon]

This does that

SELECT Distinct ClientID, ClientName From YourClientEquimentTable

To test it put it in a query and try it.
The DISTINCT keyword means bring back only unique records.
The added bonus is you will only see clients that actually have equipment if you do it this way.

If you post up a small sample database I can probably demonstrate, just enough anonymous data to demonstrate the principles.

I've enclosed my database with some fake data in. Some of the clients do have multiple objects, so I hope the problem is evident. I've left my messing around with combo boxes, but have deleted a query which wasn't going anywhere.

 

[Minty]

Before I get too stuck in, do you have a Client table, but just haven't included it?

 

[DinkyDon]

Before I get too stuck in, do you have a Client table, but just haven't included it?

Yeah I have, just deleted the records and made some new data

 

[Minty]

See the attached - change the combo value, or delete its contents.

Obviously It makes sense that you would do the combo based on ClientID, not the EquimentID in real life.
Open your form on the equipment ID selected from the list box.

 

[DinkyDon]

See the attached - change the combo value, or delete its contents.

Obviously It makes sense that you would do the combo based on ClientID, not the EquimentID in real life.
Open your form on the equipment ID selected from the list box.

I've given this a little go with my proper data, and although I can start typing in the combo box and it will auto fill any client in the data table in the combo box, the full selection of clients is minimal. Having closed the Homepage form, this now seems fine, so seemingly a random problem, do you know what might trigger this?

Is there also a possibility to make the combo box search function work for all the fields, so if I wanted to check for a specific piece of equipment and see who has it, it could be done this work?

 

[Minty]

You can use exactly the same technique.
Create another combo, and make its row source the Distinct list of equipment.

Now in your Listbox add the combo as criteria for the equipment in the same way as the Client.

Something like;
Forms!Homepage!cboEquipmentList or Forms!Homepage!cboEquipmentList Is Null

Edit: I can't think of any sensible way to have the same combo search for both.