Create a dynamic listbox which gets updated on the basis of user input

[ritesh272004]

Hi,

I am developing a small access based application. I have separated the Front end file from backend database, both of them in access.

Now the problem is i do not get the idea to create a dynamic list which gets updated on the basis of user input in a text box. I have tried using a linked table and DAO recordset, however that works too slow. Incase if the back-end and fron-end are in the same access file then this problem doesn't arise.

Could anyone please guide me.

Thanks very much for your efforts.....

Rits

[patjones]

Does everything else in your application work as it did prior to doing the file split? I ask because you seem certain that the list box problem is tied to splitting the database, which may or may not be the case.

Can you post the code that you are using for the list box update? Thanks.

Pat

[NeoPa]

Check out Front-End / Back-End (FE/BE), but you say you've tried linked tables and they're too slow. As they would be pretty similar to a single Access database in the same medium I find this hard to understand.

If you're moving your Back End database to somewhere less local as part of your split then clearly this is an issue of where to store your database rather than whether or not it's split.

It's hard to know as there's not really much detail about your problem in the question.

[ritesh272004]

Hi NeoPa,

I got what you are trying to suggest. I will try all the ways you have suggested and will come back in case if the issues persists.

Thanks very much for such an early response.

Regards,
Rits

[ritesh272004]

Hi NeoPa / Zepphead80,

I have copied my code below. This code is under the Textbox Keyup event. The same code works properly if the table is in the Front-End file.

Thanks very much for your help....
-----------------------------------

ListHeadMenu is the Listbox on the Form

SQLMenu and SQLMenu1 is the string variable declared as public
Dbmain is the DAO.Database public object
RSHead1 is the DAO.recordset public object

SQLMenu = "SELECT Name FROM MasterMain WHERE Name Like '" & TxtHeadName.Text & "*' ORDER BY Name"
SQLMenu1 = "SELECT MainCode, Name FROM MasterMain WHERE Name Like '" & TxtHeadName.Text & "*' ORDER BY Name"
Set RSHead1 = DbMain.OpenRecordset(SQLMenu1)
Me.ListHeadMenu.RowSource = SQLMenu
If RSHead1.RecordCount > 0 Then
   ListHeadMenu.Visible = True
   ListHeadMenu.Selected(0) = True
End If

[NeoPa]

You've now posted more information, but it's hard to see how this relates to the problem you asked about. As it's not another question (It's more of a statement of information only), which would need to go in a separate thread, I have no idea what to do with it or how it can help you get further help for your problem (which I assume is what you're after).

It's all a bit confusing as your explanation for the info posted is quite good, yet what links it to your question is missing.

[ritesh272004]

The code is for a dynamic listbox menu which gets populated on keyup event. On every keyup event the recordsource is filtered as per the texts entered in the textbox and passed on to the rowsource property of listbox.

I have tried using something like

SQLMenu1 = "SELECT MainCode, Name FROM MasterMain IN " & "'C:\Testing\PAData22.mdb'"

For now it seems that on local drive it works fine with the above code.

[patjones]

You have in fact posted good information, but I also don't see the connection between the problem you're having and where the data is stored. If the table name has not changed, the SQL should still work regardless of whether it's a local table or linked.

Please don't take this as a silly suggestion, but have you actually opened the linked table and visually confirmed that it links properly to the back end? If the table is not linking properly then your SQL isn't going to give you the result you expect, and in turn the list box will not activate/deactivate how you want. This is the only thing I can think of right now.

[NeoPa]

This doesn't answer my point and, obviously I suppose, leaves me still as confused as to what you expect from us. There is no question, so how can we help. It's not even a separate question in the thread (which isn't allowed). It's just an explanation of something we know little/nothing about.

[ritesh272004]

Hi NeoPa and Zepphead80

From all your suggestions, i checked all the links manually and found that one of the tables were connecting to the network folder and hence causing the issue.

I removed all the links and re-linked them with the new path which works as you people have suggested.

You guys rock ...

Thanks a lot ...

Rits

[NeoPa]

I can't claim to have been much help with your question but I'm pleased you seem to have managed to solve it in the end anyway :-)