Good day all. I have yet another question.

I believe I have not built my database correctly and I am looking for the solution to a basic question.

I have 2 tables:
tblinventory (holds most of my data)
tblmodel (holds model numbers and makes)

frminventory (used to have the record source of qryinventory which was basically a query with all my fields and the lookups done via relationship)

Here is a filtered copy of the SQL view of the query
SELECT tblInventory.ID, tblInventory.Asset, tblInventory.Model, tblModel.ModelName, tblModel.Make,
FROM (((tblInventory LEFT JOIN tblModel ON tblInventory.Model = tblModel.ModelId)



What I want to do is point the form to the table instead of the query and have the textbox on the form do the "lookup". Can someone tell me how to do this?

I don't know what to set the control source/properties of the textbox. I have tried with the expression builder and I am not sure how.

I assume you say the 'Control on the form' you are talking about the control that shows the model name and make. This could be done with a domain lookup. This will display the data but a domain lookup is one way only. It shows the data, but will not let you modify it. If this would be fine then let me know and I will show you the code to put in the controls.

If you want to have the information read / write you will have to either:
a) Make the recordsource for the form contain the link
or
b) Make the model name and make controls on a subform, have the subform contain the table tblModels as it's recordsource and then use the link master / child fields to create the link on the form.

Let us know which of these methods bext suits what you want to do and we will show you the way to do it.

Just out of curiousity, why isn't the model data inside the inventory table to begin with?

Craig

One is read only, the other does need to be modified, but the good thing ( I think) is that it will only be changed via code. That one I had forgotten about.

It's the updated by field and this is the current code.. when the form is using the query as the lookup
Me!UpdatedBy = DLookup("UserID", "tblUser", "FullName='" & gFullName & "'")
I guess I would change it to:
tblInventory!UpdatedBy = DLookup("UserID", "tblUser", "FullName='" & gFullName & "'")
but will this only update the filtered record of the complete table?

Marc

Yes this would only update the one field, but then that is how I would expect you would want it to be. Are you filtering the inventory that is shown on each form or does the form show all inventory as a continuous form?

Craig

Basically I have a search form that then opens the inventory form filtered with only 1 record.

Hawk, I would recommend revisiting your table design before starting to develop work around for poor design. The workarounds will continue to pile up and will eventually become a nightmare. It would be best to fix your structure first.

When you say youhave 2 tables, does that mean in all, or just pertinent to this situation?

J

just pertinent to this situation.

I understand my table design is poor, but it's the best I can do with the knowledge I have. This database is only a temporary thing and will eventually be replaced by a commercial product.

Marc

Not trying to come across wrong, but at one time, poor design ws the best of my knowledge, so I asked questions here at UA, grew and leanred, listened to excellent advice and redesigned when it was recommended to do so, slowly becoming more knowledgeable at design and normalization. Temporary or not, building something the right way is still the way to go.

I'll say the same thing I tell my son and even myself when i slip into that mode of thinking, it doesn't matter who else is going to see it, use it or how long it is supposed to last, if you're going to do it, do it right or don't do it.

J

I understand, but I don't have the time to completly rebuild the database at this time. Which is why I am seeking the method to do this step.

Marc

So, you got it setup to open to the one record you need now, what else are you trying to accomplish?

To avoid using a large query. I think it's best to have the form source to be the table. I have tables used as lookups. Meaning that some of the data stored on the inventory table is ID numbers, modelID and the model table (tblmodel) has ID, ModelName, Make

On the form, I want to display the model name along with the asset number, serial number...and such, but if I have the control Source of the textbox for model set to model then it displays the stored value and not the actual model name.

Does that make sense?

Marc

Well, you can use the DLookup method pointed out by Craig or change your ID controls on the form (ModelID etc) to comboboxes. Set their rowsource to the appropriate table(tblModels) Click the Build button to the right of the property setting (three dots) In the query builder window add your ID field as well as the ModelName. Close the query builder(no need to save the query as a named query). Back inthe control property window, set your ColumnCount to 2, widths to 0;1.5 or wahtever for the second measurement that will allow you to see all of your model name. Make sure BoundColumn is set to 1(this is the default). Now, when you look at your form in Form view, you should see Model Name instead of a non user friendly ID number.

J

... Instead of the Query whose SQL String you posted earlier??? No, it doesn't make any sense.

You stated earlier that the Form [frmInventory] is filtered to only one record, perhaps using the WhereCondition of the OpenForm method, then you don't have a "large" Query (meaning a lot of data retrieved), anyway.

In fact, it is less efficient to use the Table and separate DLookups to retrieve ModelName and Make since these will require separate accesses to the data, unlike retrieving all data for the one returned row using the Query / SQL as posted originally (see later for Join in the Query). See the recent discussion in UA Thread 1761111.

I can't see why you need to use the Left Join since an Inventory item must be related to a Model record (If you don't have R.I. in the Table design then you should add R.I.). Perhaps, you should use the Inner Join instead...

Very good point about the DLookup vs. SQL rowsource....

Each DLookup will incrementally slow down the performance of your form. I don't think however the combobox scenario will as much. I probably should have clarified(thank you Van for catching that) that there is nothing wrong with having the query to grab all your fields from seperate tables as the record source for your form. However, if you are using the form for data entry, you will have to make sure your SQL still creates an updateable record source.

J

I tried using the combo box method and it works, but how do I remove the arrow on the right of the box, the one that would expand the pulldown list.

I do not really understand what you are saying in your last message when you talk about using a query to grab all my fields from seperate tables.

Previously I had 1 query that would grab all my fields from the inventory table as well grab all the data from my other tables. But it made it a very big query which I think is partially what slowed down my database. (since it is running from a network share)

only 1 of these other tables has data to be updated, but it will be updated via code on a button and not directly on the form. In that case will it work or do I need an updateable record source?

Marc

Yes, you can update that table using code behind a button on the form. You can't get rid of the arrow. Are these going to be updateable fields or are you disabling these controls? If they are going to be disabled, you could draw a rectangle over the drop down arrow and make it the same color as your form background.

J

These are non updatable fields. These fields will only be used to display information.

The only one that will be updated will be "updatedby" field

Can you confirm that if I use the following code it will only update the filtered record and not all records

tblInventory!UpdatedBy = DLookup("UserID", "tblUser", "FullName='" & gFullName & "'")

where gFullname is a global variable assigned by the startup form where it captures the logged in user then checks against a "users" table


BTW, Happy new year.

Marc

Marc, I don't think that willdo what you want. You should put the UpdatedBy control on the form, make it invisible if need be. Using the OnDirty event maybe of the form(I think that is the one that will only fire if a change is made to some control on the form, use the following code:

Me.UpdatedBy = DLookup("UserID", "tblUser", "FullName='" & gFullName & "'")

That should get you rolling.

Happy New Year to you as well! Hope it was safe and fun!!

Well seems as though this is becoming a nightmare as you stated. I am trying to do something else and I am getting an error message saying the table is already open exclusively. I am guessing it's because the form is using the table as the source rather than a query. Is this true?

If so. I might need someone to take a look at my design to see why it's so slow. If I put the back end locally it runs at a decent speed

Marc

Edited by: hawk7890 on Tue Jan 6 13:48:48 EST 2009.

I found out my mistake and I have resolved it.

Thank you all for your help