Access DLookup to Search Entire Table (Field+Row)

[Mass144]

Good day people!

I am trying to do a Dlookup or any lookup option on my form.

so I already have my form with a search option that works fine and get the date I want from my table.

The problem is that I am trying to put a text box or what ever I should put so that box shows the info from another table. I know it doesn't sound clear put here i'll simplify!

Form: List is link to
Table "Personnel list"
Field: Cubicle number, Last name, etc
On my form, I have the search option that according to the text I put, it gives me the cubicle number and the last name that correspond to my search (That option works fine)

Where the problem is:
I have another table named "Cubicle number" that have all cubicle number as a field
Field: AA01, AA02, AA03 (each field only have 1 name in it)

What I am trying to do is put 2 more text box on the same form so if I write "Mason" in my search criteria, it will look the entire "Cubicle number" table and gives me the date (Field and row) that matches the search

[twinnyfo]

Mass144,

Welcome to Bytes!

I am afraid I am still having problems understanding your question. Please review this link on Asking Good Questions first, and then provide more informaiton.

Concerning your last paragraph:

What I am trying to do is put 2 more text box on the same form so if I write "Mason" in my search criteria, it will look the entire "Cubicle number" table and gives me the date (Field and row) that matches the search

There may be a misunderstanding on your part as to how a search works. MS Access does have Fields, but it does not have "rows"--as MS Excel has. MS Access can return the index of a particular record, as usually a record has an index identifying each record as distinct.

Additionally, it is unclear from your post as to what the fields AA01, AA02, etc. are used for.

Any additional information you can provide for this would be helpful, as you have not really given us anything to work with.

Thanks.

[Mass144]

so basically... I'm trying to do a search option based on the entire table instead of field specific.
I want that search to give me the field name and the data associated to my search

[twinnyfo]

DoCmd.FindRecord "Smith"

will find the first record with "Smith" in it. You may have to play around with this a bit to get exactly what you are looking for.

However, we can work through additional details if need be.

[Mass144]

[IMG]C:\Users\Masson.BJM\Desktop\try.JPG
[/IMG]C:\Users\Masson.BJM\Desktop\Try 2.JPG

ok so basically, where would I do that formula (here's screen shots of my database.

Picture 2 is my table itself
Picture 2 is Form.
I have a text box to enter my searching criteria, a button search to apply my filter and to box that needs to generate info that matches my search criteria.

[Mass144]

sorry, here's the pictures! I have mistaken picture 1 and 2

[twinnyfo]

This would be placed into the VBA Module of the Form.

Based upon the picture you attached, your Form does not look like it is bound to the table that you are searching in? I can tell this from the "Record 1 of 1" at the bottom. If that is the case, the .FindRecord will not work.

The image of your table is too small too read, so it is not helpful. This is not your fault; the forum resizes images to no greater than 500 pixels wide.

However, while I can appreciate your intent here--that is, being able to search for a term anywhere in your DB--this is not usually the best way to do it. We usually know that we are searching for someone's name, so we search in the "Name" field, etc.

Likewise, when you have a form bound to a table (or query for that matter), and you have the navigation buttons at the bottom of the form, as you do, the easiest way to search for "anything, anywhere" is to use the search box at the bottom of the Form. I wonder why this does not prove suitable for your needs? There may be reasons you need to find anything, anywhere and return values for use elsewhere in your project, but so far, we have very limited information about what you need.

I hope these questions can help you think about your project and provide additional information as needed.

[Mass144]

That Su*** about the pictures. if it can help you, here's what it would look like.
Table name: Cubicle number
Field: AA01 AA02 AA03 AA04 AA05 DD02
Info: Masson Poirier Gillett Vena Dupras Fortin

so I need a form with a search option that either I look up the field name DD01 or Fortin, both (Field name and info) will pop in a text box or what ever I need to put for it to work.

BTW... I really appreciate your help!

[twinnyfo]

So, if I may ask, because this is an assumption I must make about your data.....

The "Field Name" is actually the Cubicle number? And the Person's name (The "value" of the Field) is the person sitting in that Cubicle?

[Mass144]

Yes exactly. I don't really like how it is designed but I have another form that require this table to be set up that way and the reason why I am trying to set up a search option is just to help me when somebody is switching around or if we have new people, I can just do a search and update info instead of playing in the table itself.

[twinnyfo]

First, since this forum isn't usually very well known for pulling punches, that sounds like a terrible design.

If another form requires that the table be designed that way, then that's a terrible design for a form.

I can't conceive of a design in which this would make sense.

None of this is intended as a personal attack--just stating the facts.

A proper design for the table would be a Table named tblEmployees, with the fields: EmpID, EmpName, Cubicle, etc. Then, you can search for Employee Name or Cubicle. Right now, you can't search for the cubicle, because it is a field name, not a field value.

Additionally, what happens when you add employees? You must restructure your tables and forms to add additional fields. I know the argument always goes the same: "But we're not planning on ever adding any new employees." That is immaterial. Proper DB design is proper DB design, and those of us who have been around the block a few times are all too familiar with the headaches caused by DB designs that don't follow those principles--so, we steer other users well away from them.

I can continue to help as you wish, but I will continually redirect you to a better DB design than what you have.

I hope this makes sense.

[Mass144]

actually i know it is a bad design. i already have another table which have the peoper design. the only reason why i created that bad design is because i have a floor plan with every single cubicle which that kind of design is way easier to use as i just have to drag the field on my floor plan to get the name associated to it. and no i can't add new people as all cubicle on my list are idebtified as somebody's name or vacant. i use proper designed table for all other forms i need running but using that poor design table only to facilitate upload on my floor plan. but its ok, i'll just have to update the info manually in that table to update my floor plan

[twinnyfo]

I'm not sure I understand what you mean about dragging fields onto your floor plan?

Perhaps if we understood what it is you do, we can understand what it is you want to do and then we can move forward toward a solution.

I admit, your explanations are making things more confusing, rather than less confusing.

Very seldom do circumstances mandate a poorly designed table/form. There are usually easy ways to work things the proper way. I am hoping to guide you toward that solution.

[NeoPa]

Mass144:
The only reason why i created that bad design is because i have a floor plan with every single cubicle which that kind of design is way easier to use as i just have to drag the field on my floor plan to get the name associated to it.

What makes you think this can even possibly make sense? Bad designs are bad because they don't work well for you. If you had a design that worked better for you then it wouldn't be a bad design. What you've outlined is.

I suggest that if you were in a position to judge reliably then it's unlikely you'd be here asking for help to get it to work. Don't be one of those people that knows too much to listen, but not enough to prove it.

[Mass144]

ok... I have my database set up in good design mode for all tables i'm using which i have a field for cubicle number, a field for last name, a field for first name and so on, i also have a form with a search option for those tables. I also have a form called (floor plan) which have my floor plan as a background and need to put 196 text fields for every cubicle number and the last name associated with that cubicle number. The reason why i have a different table with every cubicle as a field name and last name as the only info is because when you bound that kind of bad design table to the form, you can go in design mode and just drag the field into the form to get the data so if i drag the field named AA01 well i only get the last name of AA01 where i want it without have to do big calculation in Dlookup and so on. I know it is a bad design but still works extremely good for the purpose why i'm using it that way.

[Mass144]

and what i am doing when i have a new member or a member changing cubicle is using my normal form to update my normal tables but i was trying to merge that (bad design) table to it so i can update both at the same time thats it but it is trouble doing it so i'll just open the table and change the option manually instead of doing it from a form. that way my floor plan form will still be working and data will still be accurate as long as i update the table it is bounded too

[NeoPa]

I say "Don't be one of those people that knows too much to listen, but not enough to prove it.", but obviously that's your choice. It's not for me to tell you. I simply make the observation it may be a wiser approach.

[twinnyfo]

One way to use a properly structured table and your seating chart form is to create a CrossTab Query in which the cubicle IDs are the column values and the person sitting in the cubicle is the Value. You would have to use a place holder for the Row and "aggregate" the employees by using the "First" value. Your query would look something like this:

TRANSFORM First(tblCubicles.EmpName) AS FirstOfEmpName
SELECT "Cubicles" AS Placeholder
FROM tblCubicles
GROUP BY "Cubicles"
PIVOT tblCubicles.Cubicle;

This would give results like this:

Placeholder  A001  A002  K001  K005
Cubicles     Jack  John  Suzy  Billy

This would allow you to use the query as the record source for your Form.

Typically, I don't recommend using a Cross Tab Query as a Record Source, because if any of the data changes, then the Field Names change. However, if your table of cubicles has only a set number of cubicles and you always maintain the same Cubicle names, you should be OK.

This allows you to search the EmpName and Cubicle Field.

Hope this hepps!

[NeoPa]

Another reason to avoid CrossTabs is that the results are not updatable. Fine for showing data, but little use for editing it.

That said, if you use the IN subclause of the PIVOT clause then you can specify your column headings - including extra columns if required even without any data, and restricting which data is shown if there is more than you specify.

See Finding Jet SQL Help for tips on how to get help for specific types of query. This was written before I used ACE much at all but mostly when things refer to Jet then they can also be used for ACE.

[twinnyfo]

Huh! I just learned something today! I just played around with that a bit and using IN works magnificently!

Who woulda thunk?

[NeoPa]

Always happy to help Twinny. Shame not everyone can benefit from that.