Unupdatable Recordset

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

j.t.w@juno.com - 29 Jun 2007 01:47 GMT

Here's the table structure...

dbo_IMITMIDX_SQL
item_no
item_desc_1
item_desc_2

dbo_IMINVLOC_SQL
item_no
avg_cost

On a subform, I'm wanting to display a recordset to allow users to
update the avg_cost field. This could easily be done if I just
displayed the item_no and avg_cost from the dbo_IMINVLOC_SQL table.
But, I'd also like to display the item_desc fields as well to make it
more intuitive when users update the avg_cost field.

Here's the query I'm working with...

SELECT dbo_IMITMIDX_SQL.item_no, Trim([item_desc_1]) & " " &
[item_desc_2] AS [desc], dbo_IMINVLOC_SQL.avg_cost
FROM dbo_IMITMIDX_SQL INNER JOIN dbo_IMINVLOC_SQL ON
dbo_IMITMIDX_SQL.item_no = dbo_IMINVLOC_SQL.item_no;

Does anyone have any suggestions on how I can accomplish this? Also,
please let me know if I should be posting this message in a different
group.

Thanks,
j.t.w

Reply to this Message

Scott McDaniel - 29 Jun 2007 11:51 GMT

>Here's the table structure...
>
[quoted text clipped - 23 lines]
>please let me know if I should be posting this message in a different
>group.

Remove the calculated field (where you concantenate desc_1 and desc_2).

Further, why have 2 description fields if the obvious intent is to string them together?

Finally: if avg_cost described the Item, then this data might belong in the "main" table ... not sure about that, since
I don't know your data, but it would seem to me that a single item can have only one "average cost" ...

>Thanks,
>j.t.w

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

Reply to this Message

j.t.w@juno.com - 05 Jul 2007 20:00 GMT

On Jun 29, 12:51 am, Scott McDaniel <scott@NoSpam_Infotrakker.com>
wrote:

> >Here's the table structure...
>
[quoted text clipped - 38 lines]
>
> - Show quoted text -

Scott,

Thanks for your response.

I've tried taking away the item_desc fields completely but, still it
does not work. Once I link the two tables the recordset becomes
unupdatable. Is there a way around this? Or what other suggestions
could I look into?

FYI. I'm working with an ERP, which does not give me the flexability
to change the table or database structure. So, you're right, it would
seem that the avg_cost should be in the IMITMIDX_SQL table (which is
the master item table). That would go for the two description fields
as well.

Thanks again.
j.t.w

Reply to this Message