Requery AfterUpdate in Subform Wipes Out Previous Selections

[ataylo27]

I am using Access 2013.

I have two combo boxes in a subform. The first combo box, Facility, limits the contents of the second combo box, Unit, using an AfterUpdate macro that simply employs the requery command and references the Unit control.

The record source for the Unit combo box contains a select statement that refers back to the Facility selection (SELECT tblFacilityUnit.UnitID, tblUnits.UnitName, tblFacilityUnit.FacilityID FROM tblUnits INNER JOIN tblFacilityUnit ON tblUnits.UnitID = tblFacilityUnit.UnitID WHERE (((tblFacilityUnit.FacilityID)=[Forms]![frmErrorMain]![frmErrorDetailSUB].[Form]![Facility])) ORDER BY tblUnits.UnitName, and it is working just fine --- at least for the first record.

I am using a datasheet view form for data entry.

When I enter the FIRST record in the datasheet, everything works great. However, when I move to the second (or any subsequent record), as soon as I make a selection in the Facility combo box (forcing the AfterUpdate event to fire, the contents of the Unit combo box for EVERY record (other than the current record) are "wiped out."

The data doesn't disappear -- it is still actually in the underlying table -- I just can no longer see it in the form. Clearly, that is not what I had intended. Users need to be able to see the data they are entering.

I would greatly appreciate any suggestions!

 

[CJ_London]

it sounds like you are using a continuous subform - the rowsource for the combo is common to all rows so if your first selection produces A, B, C and you select A, then when you change the selection to D,E, F the A is no longer in that list so is not displayed.

You need to use different events - take a look at this link to see what is done there

http://www.access-programmers.co.uk/forums/showthread.php?t=275155

 

[ataylo27]

I am using a datasheet data entry subform.

I have reviewed the information/demo database to which you directed me, but I'm having some real difficulty figuring it out.

Your example includes some notes, but, honestly, I don't mind being spoken to like I'm a three-year old, if that's what it takes for me to "get" this. :banghead:

Can you "dumb this down?"

 

[CJ_London]

there are only a few lines of code....

The example shows a mainform with two subforms. Each subform uses the same sourceobject - tblOrders - one in continuous form view one in datasheet view for demo purposes only - the code is the same.

So you need to look at the tblOrders form

You say you have two combo boxes - Facility and Unit

Facility is the equivalent of the cboColour control and Unit the equivalent of the productFK control

So remove all your existing code you have on Facility and Unit control events to 'clear the decks'

Then copy the afterupdate code from cboColour to Facility but where it says 'ProductFK' change to Unit. You don't need the enter and exit events since these are for filtering cbocolour based on the producttype column

Now do the same for the Unit control gotfocus event- plus copy the popproduct sub - you can rename it to something more meaningful and will need to change the sql to what you need

Finally add a popproduct call to your formload event

 

[ataylo27]

Thank you - that helped considerably.

Now, in this scenario, my Facility = your Colour. I think that means my Unit - your Product?

My row source for Unit is: SELECT tblFacilityUnit.UnitID, tblUnits.UnitName, tblFacilityUnit.FacilityID FROM tblUnits INNER JOIN tblFacilityUnit ON tblUnits.UnitID = tblFacilityUnit.UnitID WHERE (((tblFacilityUnit.FacilityID)=[Forms]![frmErrorMain]![frmErrorDetailSUB].[Form]![Facility])) ORDER BY tblUnits.UnitName;

When I try to incorporate that into the popProduct sub (I've renamed to popUnit), I'm not getting an error, but it also isn't working. I've pasted below:

Private Sub popUnit(Filtered As Boolean) 'technique 2
Dim sqlstr As String
'use this technique where the combo box does not display the bound column once selected
'refresh the rowsource to show all options
'no need to sort since this is a simple lookup, never displayed as a list
sqlstr = "SELECT tblFacilityUnit.UnitID, tblUnits.UnitName, tblFacilityUnit.FacilityID FROM tblUnits INNER JOIN tblFacilityUnit ON tblUnits.UnitID = tblFacilityUnit.UnitID WHERE (((tblFacilityUnit.FacilityID)=[Forms]![frmErrorMain]![frmErrorDetailSUB].[Form]![Facility]))"
Unit.RowSource = sqlstr
Unit.Requery

'now change the rowsource filtering to include the upstream combos
'- this will only affect the current combobox until it loses focus
If Filtered Then
'wait for initial refresh to complete
DoEvents
'apply filter and order - note the use of the combobox name
'- this only works if the rowsource is written directly as sql and not as a query
Unit.RowSource = sqlstr & " WHERE Unit=[Unit] AND Facility=[Facility] ORDER BY Unit"
End If

End Sub


When I try to use all of this in data entry, my Facility combo works fine, but my Unit combo is consistently blank - no options appear from which I can select. What am I missing?

 

[CJ_London]

you don't need the where clause for the simple list string

'no need to sort since this is a simple lookup, never displayed as a list
sqlstr = "SELECT tblFacilityUnit.UnitID, tblUnits.UnitName, tblFacilityUnit.FacilityID FROM tblUnits INNER JOIN tblFacilityUnit ON tblUnits.UnitID = tblFacilityUnit.UnitID"

and looks like this needs to be

Unit.RowSource = sqlstr & " WHERE Facility=[Facility] ORDER BY Unit"

 

[ataylo27]

Two steps forward, one step back.

Seems like I'm getting closer, thanks to your help! But, it's still not quite right.

Would you be willing/able to take a look at my DB?

 

[CJ_London]

if you like, tho' I'm very busy at the moment - I can take a quick look and if it is a quick fix, great, otherwise it will be a few days.

before sending, remove or disguise any confidential data. Also remove non relevant forms, tables, queries and reports. Then compact the db - also probably best to zip it before posting

 

[ataylo27]

Many thanks! I hope it's a quick fix. I think I'm close - just missing something simple. However, admittedly, with this particular thing, I'm a little out of my league.

 

[CJ_London]

Found it I think - in the popUnit sub, your filter isn't quite right - you missed ID off the end of Facility

Unit.RowSource = sqlstr & " WHERE FacilityID=[Facility] ORDER BY Unit"

also Unit should be UnitName

Unit.RowSource = sqlstr & " WHERE FacilityID=[Facility] ORDER BY UnitName"

 

[CJ_London]

Also just realised you have not done this from my post

Finally add a popproduct call to your formload event

- should be popproduct(false)

 

[ataylo27]

That was IT! I literally found it myself two minutes before I received notification of your reply!

I got up this morning and decided to recreate everything from scratch - that's how I found it -- AND that's where I added the missing popproduct FormLoad event!

Sometimes, you just have to start all over to figure out where you went wrong! It doesn't hurt to have geniuses available to you across the web.

THANK YOU!!!!!

 

[CJ_London]

glad to help

 

[ataylo27]

One last teensy issue .... and I've uploaded a new copy of the DB to demonstrate.

It has to do with the Error Code field. This subform combo box should flex based on the selection of QC or QR from the MAIN form.

I'm using an IIf statement in the underlying rowsource query and it appears to be working correctly -- at least in terms of what displays in the combo box. However, I cannot make any selection other than the first one on multiple records in my subform.

Again, any help would be greatly appreciated!

 

[CJ_London]

because you have the rowsource selecting the errortype, not errorid. Should be

SELECT tblErrorCodes.ErrorID, tblErrorCodes.Error FROM....

Also you may need to create another sub like popUnit plus appropriate calls to restate the errorid rowsource, if the user unticks the QC option.

 

[ataylo27]

Yes - that was it! Thanks again!