List box on a form

[adiemus2001]

Hey everyone.

I have been given the task of creating a Access 2K data base that involves some tasks that I feel out of my depth with but as I want to learn, I intend to keep trying.

My problem sounds simple on paper but suspect it is not the case when it comes to making it happen.

I have a list box on a form into which a user enters the name of a product. I have managed to set up a code using the "OnNotInList" event of the box so that if the product type is not in the list, the user gets asked if they wish to add it as a new product, then they just go to each
of the fields and enter the information that pertain to that new product - that works just fine.

But, I also need to have the reverse happen so that when the user has entered the product type and hits the return key, if the product type *IS* in the list, a further list box pops up with a list of simular products and all they have to do is select the one they want and access2k fills the rest of the product information in for them from the existing record.

Sounds easy doesn't it but you guys know better than I that it is not. Any pointers or suggestions would be great and if it would help, I can send a small copy of the DB so you can see what I need to do. But please remember that I am a relative newbie to Access and need things in laymans terms.

My grateful thanks

Kenny Jaymes

 

[KenG]

I'm not feeling kind, but I'll try...
What exactly do you mean when you say "if the product type *IS* in the list, a further list box pops up with a list of simular products "

My guess is the first list box a set of categories and the 2nd list box is products from the selected category?
Is that correct?
If it is correct I might try using a form for the categories and a subform for the products, linked by the common field (I'm guessing it's the category)
Also, you might want to split the category data and the product data into 2 tables.

That's my first guess/suggestion

 

[nomus1]

Hi. Would this work?
Listbox #1
Onclick event that makes a second list box visable
Listbox #2
Rowsource filtered based on item selected in listbox #1

Have the user double click the item that they want from listbox #2 and on the double click event populate the fields in the form with the item selected.
Based on the rowsource for the form you might be able to use the seek method to populate the form.

 

[BoxHead]

Nomus, Adiemus, I was thinking about the same thing, but if you can add an item to the first listbox, how could you filter by the item?

Adiemus, could you provide a bit more explanation of the table structure and specify what you mean by "similar"?

Thanks,

John

 

[nomus1]

If you had just added the item to the list, you would not have any data to populate your second list box with, so you would go right to add new and have blank fields.

You would only do the second list box bit if you had not added a new item.

 

[adiemus2001]

First, Can I thank everyone for their replys - I really do appreciate it, thank you.

John: ok here goes with the specific detailed explanation.

The database has three main tables: tblRecordings, tblTrackInfoMain and tblTrackInfoSub. Any repeated data is stored in further tables linked back to the appropiate one of these three by look up fields. For example: tblRecordings has a field called Recording title. As this refers to a name of a CD as a whole but also is linked to the individual tracks on the CD (Track one is on the CD with the Recording title of "********&quot, it is stored in a seperate table.

tblRecordings contains info specific to the title of the CD: Library NO, CD Title, Catalogue No, Release date, Total Running time and so on.

tblMainTrackInfo contains info specific to the artists version of a given song: Artist Name, Track No, Song Version and is linked to tblRecordings by a "one to many" relationship using a RecordingID autonumber.

tblTrackInfoSub contains info that is specific to the song (baring in mind that the same song could be recorded by many artists): Track Title, Length, Composer, publisher and so on and this is linked to tblMainTrackInfo by "many to one" relationship on a TrackTitleID auto number.

It has a main data entry form that contains fields from tblRecordings, with a Tab page that has a subform containing fields from tblMainTrackInfo. This subform also has a subform containing fields from the table tblTrackInfoSub.

This all works GREAT, Access stores the right information exactly where I want it to, the form looks great, is easy to read and use BUT the one drawback is that without some way of checking if the information for a given track is already in the Database and if it is, assigning it to the new CD, then the user could enter double song info.
For example - Elvis presley - The wonder of you could be on more than one CD.

So to combate this, when a new song title is typed into the songtitle field and the user hits the return key, I need access to check if the song exists (hence the OnList thing). If it does not exist, access can fire a NotInList event, prompt the user to add the new track and add it if the user clicks yes. (This part I already have worked the code out for).

Now, as a song title is not unique and several different songs can exist with the same title, if access does find songs with the same title, I need it to bring up a list box of the songs it has found together with specific details of the songs (Composer would be one) as this would aid the user in chosing which song from the list, if any, is the same as the one they are trying to add.
If the user see's a song on the list and they want to use it, they just double click on it and access auto fills in the rest of the track details from existing data.

To put it in basic terms, I guess this is a "populating a form using a selection from a listbox" problem but with some added requirements. For example: If the user is entering details of a song called SONG1 (with a TrackTitleID of 2) for a new CD called CD2 and access finds that SONG1 is already associated to a CD called CD1, it will also associate it by its TrackTitleID with the new CD as well. so that TrackTileID 2 is associated with both CD1 and CD2.

Looking back on what i have typed here, I can see that it may look a bit overly complicated but I assure you that if you saw the actual Database, you would see that it works perfectly apart from the searching and adding existing records part. Believe me, I have spent the best part of 3 weeks trying to get this to work but my skills are just not good enough yet.

I dont know if my explanition has helped and made things clearer or not but if i can give any more info please do not hesitate to ask.

I really appreciate any help I can get and I understand how busy you guys are.

Kind regards,

Adiemus.

 

[BoxHead]

Adiemus,

Congratulations on building what sounds like an extremely useful and sophisticated tool.

There seem to be two issues here. First, you need to populate the listbox when a song title matches an existing title in your DB. Second, you need to associate subsequent entries of a song title to the TrackTitleID (autonumber) of the original entry.

Populating the ListBox

I built a simple 3-table db for Power Tools to try this out.

One table called tblTools with 6 fields: ToolID (autonumber/key), ToolName, ToolModelNum, ToolMfr, ToolPrice and ToolPic.

One Table for Tool Manufacturers that serves as a lookup for the ToolMfr field in tblTools.

One Table for Tool names that serves as a lookup for the ToolName field in tblTools.

I filled tblTools with 17 records and laid out a form called frmToolSelection. On my form, I included an unbound ListBox "List9" and an unbound combo box; "Combo5".

I set the Combo5 RowSource to the Tool Manufacturer's table (SELECT DISTINCTROW [tblToolMfr].[ToolMfr] FROM [tblToolMfr] This allows me to select a manufacturer.

I built a query ("qryTools&quot which is basically a copy of my tblTools because I figured that with the amount of data you're managing, you would be better off running a query for your specific info. I just wanted to make sure it would work off a query before suggesting it.

On the BeforeUpdate event of Combo5, I ran:

List9.RowSource = ("SELECT DISTINCTROW [qryTools].[ToolID], [qryTools].[ToolName], [qryTools].[ToolModelNum], [qryTools].[ToolMfr], [qryTools].[ToolPrice] FROM [qryTools]WHERE [QRYTOOLS].[TOOLMFR]=[COMBO5];")

When I select an item from my Combo5 list of manufacturers, List9 is populated with all of that manufacturers Tool names, model numbers, name and price. On the property pane for the listbox, I type a zero in the column width entry so the first field (ToolID) would not display.

Your WHERE clause would be WHERE SongTitle = ComboEntry.

Associating Song Titles to one another

Since your TrackTitleID is the key, I think you'll have to add another field to tblMainTrackInfo. Something like "RootTrackTitleID". When a new song is entered and it has no existing matches (first time entered in DB) the RootTrackTitleID will equal the TrackTitleID. I'm not sure if you can do this on the save event, before update or after update. It has to happen after the Autonumber TrackTitleId is filled and I'm not sure where that happens.

If IsNull(RootTrackTitleID) Then
          RootTrackTitleID = TrackTitleID
      End If

When you build your query for your listbox, include the fields you want auto-populated. You can hide them in the listbox by setting the column widths to zero. Also include the RootTrackTitleID. When the user selects a song (meaning there is a match) the Click or DblClick event should be along the lines of

     Me.ComposerName = List9.Column(3)
     Me.RootTrackTitleID = List9.Column(0)
     Me.WhatEverFieldYouWant = List9.Column(column number)

This will update the fields for your record and now all of the matching songs will have their own unique TrackTitleID along with indexed/matching RootTrackTitleID.

I hope this is helpful to you. I also hope that you appreciate the amount and quality of work you have already accomplished.

John

 

[1127]

Hi,

As You said it seems to be "overly coplicated".

I suggest to You to try to work with more then one form!

Even the Access will tell you "Try to use less controls!"

As I see You have to try to dynamically change the row source of the list boxes!

Please send me that small copy of Your mdb to tiborpalko@hotmail.com and i will send You an example!

Best regards,
Tibi

 

[adiemus2001]

hey John,

WOW - you have certainly gone to some trouble there for me and I can not tell you how much I appreciate it.

I am about to try putting your ideas into action and will post back in here how it all went.

Thank you again,

adiemus2001

 

[adiemus2001]

Hi Gang,

As promised - here is a update with regard to all your suggestions. Also I would like to thank BoxHead for his kind words of encouragment.

I tired all your suggestions and BoxHeads worked the best as I was able to see how to do what I wanted buy reconstructing the DB example he posted step by step. I know have a working list box that does what I want - upto a point.

When the user types an item in the text box (for example the letter A) and all items in the database that begin with the letter A are shown in the List box. This all works great, as my coding leaves a heck of a lot to be desired, I searched the web for an example and used that.

My problem is that when the form is opened, the list box ALREADY displays ALL items in the database until the user types a letter in, then it displays just the found items based around what the user has typed, For example:

Text Box data entry List Box displays
H Hello
Heat
Hotel
HE Hello
Heat
HEl Hello

What I need it to do is display NOTHING until the user enters a letter/s

Here is the SQL behind the list box
SELECT DISTINCTROW [tblTrackTitles].[TrackTitle], [tblMixTitle].[MixTitle], [tblComposers].[Composer], [tblPublishers].[Publisher] FROM tblPublishers INNER JOIN ((tblComposers INNER JOIN (tblTrackTitles INNER JOIN tblTrackInfoSub ON [tblTrackTitles].[TrackTitleID]=[tblTrackInfoSub].[TrackTitleID]) ON [tblComposers].[ComposerID]=[tblTrackInfoSub].[ComposerID]) INNER JOIN (tblMixTitle INNER JOIN tblTrackInfoMain ON [tblMixTitle].[MixID]=[tblTrackInfoMain].[MixID]) ON [tblTrackTitles].[TrackTitleID]=[tblTrackInfoMain].[TrackTitleID]) ON [tblPublishers].[PublisherID]=[tblTrackInfoSub].[PublisherID] WHERE ((([tblTrackTitles].[TrackTitle]) Like [txtSearchExpression]));

txtSearchExpression is just a hidden unbound text box that the SQL refers to and has NO propertys or record source set except it has "*" as its default value. I have tried removing the "*" and it fixed the problem of what data it displays when the user types in the text box but, when you first open the form, the list displays EVERY Item in the database. It only lists items by what ever the user types once the user has started to type.

I need the list to be empty when the form is open and only populated once the user starts to enter data in the text box.

Any Ideas please????

Also there is a On Change placed on the Text box that the user types in and the code is as follows:
------------Code start-------------------
Private Sub txtEntry_Change()
On Error GoTo Err_txtEntry_Change
' This builds the search pattern string as characters
' are added or removed from the entered text. Concactenate an asterisk on the
' end of the entered text and requery the listbox source.

Me!txtSearchExpression = Me!txtEntry.Text
lstFound.Requery

Exit_txtEntry_Change:
Exit Sub

Err_txtEntry_Change:
MsgBox Err.Description
Resume Exit_txtEntry_Change

End Sub
---------------------Code End--------------------------

Now I know you guys may well need more info than this in order to help me so please just ask for what you need and I will post it straight away.

If you can help me I would be most grateful as I simply do not have enough Access knowledge to work it out for myself.

Thank you for your time,

Adiemus2001

 

[BoxHead]

Adiemus,

I'm unfamiliar with (but curious about) the listbox update on the typing of each letter.

Where do you run your SQL for the ListBox RowSource? I have to imagine it's not on the BeforeUpdate of the combo as I had done it.

Wherever it is, try an If...Then or IIf statement:

If Not IsNull(YourComboBax) Then
          ListBox.RowSource = SELECT DISTINCTROW [tblTrackTitles].[TrackTitle], [tblMixTitle].[MixTitle], [tblComposers].........WHERE ((([tblTrackTitles].[TrackTitle]) Like [txtSearchExpression])); 
End If

HTH

John

 

[1127]

If You want to filter Your list in this way try the method that I call "TypeAndSearch":

You have to change the listbox rowsource in the textbox OnChange event.

The Where condition of Your Sql string must to be:

me.list1.rowsource="SELECT...... " & _
....WHERE <field> Like '*&quot; & <textbox>.Text & &quot;*';&quot;

In this way after every key stroke the list will be filtered by the text You enter in the box:

List Content
Family
Fax

You Type &quot;a&quot; = the list content remains the same
You Type &quot;am&quot;= the list content=&quot;Family&quot;

the '*&quot; & <textbox>.text & &quot;';&quot; will filter everything that containing the typed text at the end of string.

the '&quot; & <textbox>.text & &quot;*';&quot; will filter everything that containing the typed text at the begining of string.

the '*&quot; & <textbox>.text & &quot;*';&quot; will filter everything that containing the typed text everywhere within the string

The text property of the text box can be used ONLY when the text box has the focus!!!!

If You want to filter the listbox with more then one criteria then You have to do a Sub procedure and pass to this as parameters the value of every text box.
Warning if You call this procedure from the OnChange event of any of the text box You have to pass the TEXT of that text box, because in the OnChange event the text box don't change it's value!!!

THE OnChange event fires with every keystroke.

Best regards,
Tibi