424,294 Members | 1,891 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,294 IT Pros & Developers. It's quick & easy.

Unbound fields in split form?

100+
P: 122
I've approached this a couple different ways but I feel like I'm missing the simple solution.

I have a split form for data entry which is bound to my Orders table. When you enter an item number, I would like to display the text description for that item, which is stored in the Items table.

My first attempt was to simply use an unbound field and update its value whenever the user changed the contents of the item field. But doing this means whatever the description is for the current item is displayed for every record in the split form.

Next, I tried setting the value of the description field to use the dlookup function so that it pulls the description for each record. This works, but is incredibly slow. Whenever the user scrolls the form, there is a very noticeable delay before the description appears.

I know that I could also base the form off of a query that includes both tables, but doing so makes the recordset uneditable which defeats the point of having a data entry form.

So how can I get the description to display for each record?
Mar 4 '10 #1

✓ answered by Delerna

I use access 2003 but the basics are still the same

I just did a quick mock up of your 3 tables and copied your query above into a query def in my mockup of your db

It works fine for me


You do have your key fields in the 3 tables setup correctly...right?

If not then your query will not be editable because access won't know how to uniquely identify the individual records in the 3 tables. Because of that access won't even try and just make the query uneditable.


I set up the keys on the mock up like so
Items table
key......ItemNumber


Order Header
key......OrderID

OrderDetail table
key......OrderNumber
key......LineID

Share this Question
Share on Google+
5 Replies


Delerna
Expert 100+
P: 1,134
Your form should be bound to a query. That way you can join the items table to the orders table and include the items description field in the select list

I have no idea what your table/field name actually are
but something like this

Expand|Select|Wrap|Line Numbers
  1. SELECT a.OrderNo,a.LineNo,a.ItemNo,b.ItemDescription
  2. FROM tblOrderLines a
  3. JOIN tblItems b on a.ItemNo=b.ItemNo
  4.  
now you bind your form to that query instead of the table.
You probably should make the textbox on the form that is bound to the ItemDescription uneditable so it can't be changed from the orders form.
Mar 4 '10 #2

100+
P: 122
Basing the table on a query seems like the best way to go, but when I do I can no longer make edits to the data. This is the query I am using:

Expand|Select|Wrap|Line Numbers
  1. SELECT OrderDetail.Qty, OrderDetail.LineID, OrderDetail.Item,
  2. OrderDetail.Price, OrderDetail.Purchase, OrderDetail.DateNeeded,
  3. OrderDetail.OrderNumber, Items.Description, OrderHeader.Customer,
  4. OrderHeader.CompanyName FROM OrderHeader
  5. INNER JOIN (Items INNER JOIN OrderDetail
  6. ON Items.ItemNumber=OrderDetail.Item)
  7. ON OrderHeader.OrderID=OrderDetail.OrderNumber; 
I should have mentioned this earlier, but I am using Access 2007.

I found this link to MSDN article on when queries are updatable, but I don't entirely understand it. My query has a one-to-many relationship, which it lists as "usually" updatable. I don't understand the solution though. It seems to imply that only certain fields can't be updated, but on my form none of them can be.

Can anyone take a look at my query and help me to understand what the problem is?
Mar 8 '10 #3

Delerna
Expert 100+
P: 1,134
I use access 2003 but the basics are still the same

I just did a quick mock up of your 3 tables and copied your query above into a query def in my mockup of your db

It works fine for me


You do have your key fields in the 3 tables setup correctly...right?

If not then your query will not be editable because access won't know how to uniquely identify the individual records in the 3 tables. Because of that access won't even try and just make the query uneditable.


I set up the keys on the mock up like so
Items table
key......ItemNumber


Order Header
key......OrderID

OrderDetail table
key......OrderNumber
key......LineID
Mar 8 '10 #4

Delerna
Expert 100+
P: 1,134
Incidentally, if you are not too far along with your design and probably even if you are.

You should give youself a way to know the type of an object just by looking at its name.
Hungarian notation (google it) is one of the best that a lot of developers use

so your
tables will be prefixed with tbl
queries with qry
forms with frm
reports with rpt
text boxes with txt
comboboxes with cbo
option boxes with opt
string variables with str
integer variables with int
etc etc


The reason is, access projects...well projects developed with any tool really... quickly become filled with lots of tables, queries reports etc etc.
When you have to problem solve the database in 12 months time and you look at your query above and you see

.... FROM OrderHeader .....

Is that a table or a query? hmm I can't remember. So you search the tables and its not there. Ahhh must be a query...yep there it is.


If however you saw this
.... FROM qryOrderHeader .....
LEFT JOIN tblItems


then you instantly know where to find each object.

It also makes it easier for me if one day I take over your Job or vice versa


Just a word to the wise ;)
Mar 8 '10 #5

100+
P: 122
You do have your key fields in the 3 tables setup correctly...right?
This was the key (pun intended.) I did have primary keys set up in each table which I thought would be enough. After going into the relationship window and specifically telling Access how the tables relate to each other, I can now edit the data from the form with no issues.

Re: hungarian notation, I do that with forms and controls but for whatever reason am not in the habit with tables and queries. I am making an effort in this project to follow maintainable practices though and will start here. (I am more used to using Access for one time data manipulation than creating things for continued use.)
Mar 9 '10 #6

Post your reply

Sign in to post your reply or Sign up for a free account.