Requery single record in datasheet view

[Access Newbie Nick]

I have a form with a subform, the subform is in datasheet view. On the
subform I have requery macros that refresh the source of the next field,
cutting down on the options. For example i select a company name, then in the
next field only the purchase orders related to that record should appear.
This works fine for only one record, and also in single form view. However if
I make another record with a different comapany name in datasheet view, the
above record is also requeried and the field for purchase orders goes to
blank.

Is there any way to refresh data in only a single record in datasheet view,
preferably without using VB, but if needs be i will use it.

Thanks in advance for any help!

 

[bhicks11 via AccessMonster.com]

Try using GoToControl, SetValue in a macro or VBA.

Bonnie
http://www.dataplus-svc.com

 

[Access Newbie Nick]

actually... it seems it doesnt work in single form view either

 

[Access Newbie Nick]

Thanks for the input but i don't think i can use that in this case. If there
is a way could you please give more detail.

 

[bhicks11 via AccessMonster.com]

What are you setting the value too?

Bonnie
http://www.dataplus-svc.com

 

[Access Newbie Nick]

Another update.... Row Source is something like this

SELECT [PO Item].[Item ID], [PO Item].[Order Item], Parts.[IE Number]
FROM Parts
INNER JOIN ([PO Main]
INNER JOIN [PO Item] ON [PO Main].[Purchase Order ID] = [PO Item].[PO Main
ID]) ON Parts.[Parts ID] = [PO Item].[Item Number]
WHERE ((([PO Main].[Purchase Order ID])=[PO Number]));

The data is written to the underlying query and table ok, but does not
appear on the form correctly.

 

[Access Newbie Nick]

There is no value that i could set, the values are listed in a combo box and
there are more than one to choose from, maybe around 10 to 20 as it is now,
relating to each company. Even if i set the record source the problem still
remains as it would be a global change not only applying to the single
current record.

 

[bhicks11 via AccessMonster.com]

Try using the forms Current Event to requery just the combobox.

Bonnie
http://www.dataplus-svc.com

Another update.... Row Source is something like this

SELECT [PO Item].[Item ID], [PO Item].[Order Item], Parts.[IE Number]
FROM Parts
INNER JOIN ([PO Main]
INNER JOIN [PO Item] ON [PO Main].[Purchase Order ID] = [PO Item].[PO Main
ID]) ON Parts.[Parts ID] = [PO Item].[Item Number]
WHERE ((([PO Main].[Purchase Order ID])=[PO Number]));

The data is written to the underlying query and table ok, but does not
appear on the form correctly.

 

[Access Newbie Nick]

I mean i THINK it would change it for every record. The reason that it
dissapears may actually be due to the limit list set to Yes. I can't change
this to No as i want the first column to be bound but not to show this, 2
rows are shown 0cm;2cm.

So i could be approaching this problem from the wrong angle, not change the
macro but find some way to permanently change the limit list property to no
whilst still having the first column bound but not visable.

 

[Access Newbie Nick]

Ah yeh, sorry i should have mentioned a similar thing happens more than once
in each record.

Company > PO Number > Order Item > Designated Works Order

Each are combo boxes and it acts like a cascade effect, to use current
refreshes all only when i enter into the new record therefore does not update
when i enter a something into a field or move into a field, and still the
'limit to list' makes it so it dissapears from the form when the next record
with a different value in a field is selected

 

[John W. Vinson]

I have a form with a subform, the subform is in datasheet view. On the
subform I have requery macros that refresh the source of the next field,
cutting down on the options. For example i select a company name, then in the
next field only the purchase orders related to that record should appear.
This works fine for only one record, and also in single form view. However if
I make another record with a different comapany name in datasheet view, the
above record is also requeried and the field for purchase orders goes to
blank.

Is there any way to refresh data in only a single record in datasheet view,
preferably without using VB, but if needs be i will use it.

Thanks in advance for any help!

The problem is that on a datasheet or continuous form, it LOOKS like you have
a different combo box on each row - but you don't. There's only one combo box,
displayed multiple times! If you change its properties (e.g. its rowsource)
then it changes all of the instances of the combo box.

One getaround is to superimpose a textbox on the combo; size it carefully to
just cover the text area of the combo box, not the dropdown arrow. Set its
properties to Enabled = No, Locked = Yes, Tab Stop = No, and Control Source =
DLookUp("PurchaseOrderNo", "tablename", "<appropriate criteria>").

The combo box will "pop in front" when you tab into it or select the dropdown
arrow, but the textbox will continue to display the desired text on the other
rows.

 

[Access Newbie Nick]

Oooooo that is cheeky! I like it! It is possible to put a textbox
superimposed from the main form is it? because the subform is in datasheet
view and i dont think i can superimpose with a control from the subform. If
needs be i suppose i can put it in continuous form view and arrange it
nicely, but if possible i'd rather not.

Thanks for your answer, i hadnt thought of it from that angle!

 

[John W. Vinson]

Oooooo that is cheeky! I like it! It is possible to put a textbox
superimposed from the main form is it? because the subform is in datasheet
view and i dont think i can superimpose with a control from the subform. If
needs be i suppose i can put it in continuous form view and arrange it
nicely, but if possible i'd rather not.

Well, I very rarely use datasheet view at all. It gives you (the developer)
too little flexibility and gives the user too much - they can hide columns and
forget that they've done so, for instance!

Your choice. I don't know if you can do this trick with a datasheet, but I
doubt it.

 

[bhicks11 via AccessMonster.com]

Thanks John - I knew you would eventually straighten us all out on this.

Bon