Create a form to filter records in a table 2

[doobybug]

Hi,

I need to create a form to perform a search in a table, with any fields found in that table. Can anyone help me pls?

 

[Mayhem9]

Hi MajP,

Yes, I looked at the example and I thought that I had replicated it. I’m not sure which of my last two posts you are responding to when you ask for the code. I’m not using any code to perform the search I described. I simply tried the search option in the combo box and followed the wizard. This may be the problem?

The changes I have made to the tables are as follows: Essentially, all I have done is take the look-ups out of the table and put them in the form. The look-up fields in the table were then replaced with text fields. I created the look-ups to include both the PK and the data (e.g. ManID and Man). I referenced the look-up to column 1 (PK) and display only the data (i.e. 2 columns, with the first set to 0cm width).

This is the main table:

Tool_Log
ToolIDdb Long PrimaryKey Indexed
ManID dbText Indexed
Serial dbText Indexed
TypeID dbText Indexed
Size dbText Indexed
Description dbText
Set dbText Indexed
YearID dbText Indexed
RRP dbCurrency
PurID dbText Indexed
Date dbDate
LocID dbText Indexed
ImagePath dbText

These tables provide data that will be entered into the table above via look-ups in the form that enters the data into the table above (I’m sure there is a more eloquent way to state that).

Location
LocID dbLong PrimaryKey Indexed
Loc dbText

Manufacturer
ManID dbLong PrimaryKey Indexed
Man dbText

Purchase
PurID dbLong PrimaryKey Indexed
Pur dbText

Type
TypeID dbLong PrimaryKey Indexed
Type dbText

Year
YearID dbLong PrimaryKey Indexed
Year dbText Indexed

The form has all but two fields from the Tool_Log table and where I previously had look-ups in the table, I now have these in the form.

I apologize if I haven’t provided the exact information that you require to understand what I am screwing up but I an not exactly sure what is needed. Please let me know if this is not what you need and if not what I need to provide.

Darren

 

[MajP]

I simply tried the search option in the combo box and followed the wizard. This may be the problem?

The wizard builds code and associates that code to an event such as the "afterUpdate" event. Look at the properties page. Look at the Events tab. If there is an event with "[EvenProcedure]" click on the ellipses on the right of the property. This should take you to the form's code module. Post the code.

Can you post your db on 4shared on another site? Its free. It would be a lot easier to provide a working example and see your problems.

 

[Mayhem9]

Thanks MajP,

I have uploaded a copy of my database with 10 records. I really appreciate all your help, and those of the other users on this site.

The URL is:

http://www.4shared.com/file/200248795/2308fef1/Tool_Log.html

Cheers,
Darren

 

[MajP]

I have looked at this and it about kicked my butt because you can not see what is actually going on MS should be shot for allowing these lookups within the table.

1) You still have lookups in your tool-log table. You need to get rid of them. On each field under properties there is a tab with "lookup". Change everything to textbox

2)Now you have a choice. You have to pick one. You have picked half of each.
a. You can have an autonumber be your primary key . For example in table manufacturers. You have

1 Snap-on

If I choose the primary key to be the autonumber then I store in the tool-log the value 1. NO LOOKUP. Did I say no lookups? The forms will take care of converting the 1 into a viewable "Snap-On" either using a query or a combobox. But when you look in the table you will only see the value 1 as the foreign key for manufacturer

b. But you can make this very easy. Make your primary Key "Man" in the Manufacturer table. There is nothing wrong with that. It is short. Now evertying is a lot easier. My tool-log stores and displays "Snap-On". My combo displays only "Snap-On". I can link the manufacturer table to the log table by "Snap-On". This is usually called a natural primary key.

So if it was me. All your tables could simply get changed from

Manufacuterer
ManID autonumber PK
Man text

to
ManID Text PK (this is what used to be the Man field)

There is no reason you have to have an autonumber.


So pick one of the choices and do it where appropriate.

There are times when an autonumber is a good choice. For example a "Notes" table containing notes. I can not link on a memo field.

The problem you have now is that you are storing in your log table "Man" but that is not the primary key in your Manufacture table.

If you fix this throughout I can send you a real nice search form with a lot of utility, based on what you have.

 

[Mayhem9]

Thanks MajP,

I think I will delete the table and start again, as I did actually go through and delete the look-ups and replace them with text fields. It told me I had to delete all the relationships before I could do it, so I did. I thought it had worked?

Would have creating the look-ups in the form (using the wizard) have changed it back?

I will go with option b (the only reason I used a numeric PK was I was told to always have one). My question is, do I then use the wizard to create the look-up in the form when I add the combo box?

Thanks again,
Darren.

 

[MajP]

I think you will get many different opinions on if the primary key should be numeric or should be a natural key. There are pros and cons for both. For efficiency with very large databases ten to 100 of thousands of records, I would think a numeric key could make significant performance improvements. Also strings can be difficult to work with if they have any of the following in them (',",#). If you decide to use a text string as the PK, then if you have
Joe's #1 Tools, I would enter it as Joes No 1 Tools.

I use autonumber PKs a lot, but I think for what you are doing you will be OK. Might use an autonumber in my Size Table especially if you enter something like #16 or 3/4".

I would not use a text as a PK that has a lot of spaces and symbols.

The wizard on the form will not change it back, but to get them out once you put them in the table you have to clear out the lookup tab.

I think you will be able to use the wizard for the lookup on the form.

 

[dhookom]

FWIW, I almost always use an autonumber primary key on all tables. I don't waste time thinking about it anymore. I just add the first field as the primary key autonumber. I have too many decisions to make and don't need another. I also do a fair amount of web development and having a consistent numeric primary key simplifies my life.

Duane
Hook'D on Access
MS Access MVP

 

[Mayhem9]

Thank you both for your responses,

Might use an autonumber in my Size Table especially if you enter something like #16 or 3/4".

Ok - good info, as all AF sizes will include the - and / and " and philips screw driver tips (amongst other tings) are designated with #.

FWIW, I almost always use an autonumber primary key on all tables. I don't waste time thinking about it anymore. I just add the first field as the primary key autonumber. I have too many decisions to make and don't need another. I also do a fair amount of web development and having a consistent numeric primary key simplifies my life.

Yes - but you know what you are doing Duane!

Given that the size field will contain a lot of symbols, will there be a problem with using this field to search. Guessing from Dhookom's post I am guessing that this is possible? If I understand it, the loop-up can be linked to the string only?

I'll make a start on the other fields and await your comments before doing anything with the size field.

Cheers,
Darren

 

[MajP]

One thing. Sometimes you have tables (like you do) that only have a single piece of information. For example Manufacturer in your case only has a name not other information about the manufacturer. If you are absolutely not going to have addtional information (be careful because of requirement creep, you never know), then I call this a lookup table instead of a reference table. In that case there is not really a reason to link the tables. You just store the Man name directly in your tool log. Basically that is the option B. But if some day you are adding, Man Address, Man phone number then you might as well use an autonumber as the PK.

Again if in the size table you use an autonmumber PK, then in the log table you will store the autonumber. If 3/4" has a PK of 1 I store 1 in the log table and when I look at the log table I see a 1. But I do all my data entry on forms. On the form although the field has a value of 1 the combo shows 3/4". On the search form I might see 3/4" in in the combo, but it returns the associated PK of 1. Then I search the form for an underlying 1.

If you are NEVER going to add additional fields to the size table except the description then no need for an autonumber and you will have no problem searching.

 

[Mayhem9]

Thanks MajP,

I think I got that. Leaving the size field as a single field without a PK sounds like the best idea. After I sent my last post and opened up my table, I remembered that the size field is a field in the table and not something look-up. However, it is a field that will be very helpful to include in a search.

Something interesting just happened when I tried to remove the autonumber PK form the location table and make the location text the PK. I got an error saying that there were duplicates! However, I couldn't see them. I did have common strings in each like Top Chest (RHS) D1, Top Chest (RHS) D2, Top Chest (RHS) D3... I deleted the parentheses and it fixed the problem! So I guess I see now what you meant by the use of certain characters.

Cheers,
Darren

 

[MajP]

I looked at that table and there is a duplicate in it. You can use the "Find Duplicate Record" query wizard to build a query that shows duplicates. Top Chest (RHS) D1, Top Chest (RHS) D2, Top Chest (RHS) D3 should not be a problem

Here is the problem

LocID Loc
8 Top Chest (RHS): TT
15 Top Chest (RHS): TT

 

[Mayhem9]

Bugger! I missed that.

Almost there though - all the look up tables down to a single field/PK. Just about to tackle the Tool_Log form now.

 

[Mayhem9]

OK – well I must have done something right, as the form seems to be working, as does the search combo box that I tried on a field that is a look up. Even more, the original search form that I made by following the online tutorial works as well. However, I would be very keen to learn more about the search you referred to.

I decided to make the year a ‘regular’ field, as having it as a look-up seemed kind of a waste of time given it is 4 digits. This is only really important for some tool manufacturers and I have set a default to Unknown for this field.

Rather than open another thread, I want to ask a question about combo boxes in here, as I think it links in but will re-post if anyone feels it appropriate to do so.

Is there a way to enable the up and down arrow key to move through the drop down list?

Do you know there might be only one downfall of getting this right – I now want to learn how to do more complex (more complex for me) things!

Thanks again for all your help,
Darren

 

[MajP]

Is there a way to enable the up and down arrow key to move through the drop down list?"

Is that not the default behavior? I do not have 2003, but in 2007 that is the default behavior.

 

[Mayhem9]

Not really.

When entering a new record, if I begin to type into a combo box and it starts to auto-complete, hitting the down arrow simply places the cursor between the first and second character in whatever was displaying at the time.

If I click on the down arrow on the combo-box (i.e. to see the drop list), then I can use the arrow keys to move through the list (only just noticed this).

If I tab from one field into a combo box, the down arrow takes me to the next record and the up arrow to the previous one.

I am wonering if I can get the arrow keys t scroll through the list when I type into combo-box, as in example one.

 

[MajP]

http://www.4shared.com/file/170681739/c0708f11/FAYT_Combo_and_List_10Dec.html

Type into this combo box in the demo. See if this is a good solution. It is a find as you type combo. Which narrows down the list as you type.

 

[MajP]

Put this in the keydown event. This works. Here is the TypeID combo

Private Sub TypeID_KeyDown(KeyCode As Integer, Shift As Integer)
If KeyCode = 40 Then
Me.TypeID.Dropdown
End If
End Sub

 

[Mayhem9]

Thaks MajP,

I will take a look and give it a try. It looks like a nice feature to have, especially if the list is long.

 

[Mayhem9]

Hi MajP,

I am hoping you will help me, as I have no idea what I have done here. I imported the module into my database and placed the code into the form.

When I leave the design view on the form I get the following error:

The expression On Load you entered as the event property setting produced the following error: A module is not a valid type.

When I click OK it brings up this one”

The expression On Current you entered as the event property setting produced the following error: A module is not a valid type.

Finally, if I click any of the buttons on the form I get this one:

The expression On Click you entered as the event property setting produced the following error: A module is not a valid type.

The problem is obviously caused by this code but I have no idea what I am doing wrong:

Option Compare Database
  Option Explicit
Public faytProducts As New FindAsYouTypeCombo

Private Sub Form_Load()
  faytProducts.InitalizeFilterCombo Me.cmbProducts, "ManID", False
End Sub

Additionally, there are two lines in the code in Module FindAsYouTypeCombo which show in red and return the following error (in both cases “WithEvents” is highlighted).

The code is:

Private WithEvents mCombo As Access.ComboBox
Private WithEvents mForm As Access.Form

and the error is “Only valid in object module”.

I checked the references and Microsoft DAO 3.6 Object Library is checked.

Where am I going wrong (apart form trying to use access!!)?

Cheers,
Darren.

 

[MajP]

Sounds like you copied the code into your form's module.
The code all needs to go in a CLASS module, not a form's module or a standard module. The best thing to do is to import the class modules directly into your database. Using the import external data feature.
If in 2003 "File, Get External Data"
in 2007 it is on the ribbon.