filtered subform combo not refreshing on record navigation

[misscrf]

I have an entry form that has a page control with multiple tabs to support multiple subforms. One tab has (for example)
Artists. Another tab has Singers. Another tab has Musicians.

A 4th tab has information for record deals. On this tab, there is a combo to choose the "type". A user can choose Artist, Singer or Musician.

Based on what they choose a combo becomes visible with a filtered list of just those artists, singers or musicians on this contracts record.

My issue is that I will open the entry form and be on the first contract. I go to the record deals tab and choose Artists as my type and I see a list in the (now showing) artists combo of the artists that are in this contract record on the artists tab. All good. Then I go to the next main contract record and choose Artist as the type again. The artists list show the artists from the last record. If I start typing the name of the artist that I know is on this contract I get the error message I set up in the notinlist code and then the combo refreshes and the right contract artists are available for that record.

Wierd!!!

I need a way to make the 3 combos refresh their source filter to the current contracts lists, as I navigate from contract to contract on the main form.

this code is on the deals subform on current:

Private Sub Form_Current()
If Me.FKPartyType = 1 Then
Me.cboArtist.Visible = True
Me.cboSinger.Visible = False
Me.cboMusician.Visible = False
ElseIf Me.FKPartyType = 2 Then
Me.cboMusician.Visible = True
Me.cboSinger.Visible = False
Me.cboArtist.Visible = False
ElseIf Me.FKPartyType = 3 Then
Me.cboMusician.Visible = False
Me.cboSinger.Visible = True
Me.cboArtist.Visible = False
Else
Me.cboMusician.Visible = False
Me.cboSinger.Visible = False
Me.cboArtist.Visible = False
End If

end sub

Each cbo has a control source that limits to the parties of that type that are tied to that main contract record. It all ties correctly, but just doesn't refresh correctly.

Can anyone please help me figure this out?

Thanks!


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

Your code could be written

Private Sub Form_Current()
  Me.cboArtist.Visible = Me.FKPartyType = 1
  Me.cboMusician.Visible = Me.FKPartyType = 2
  Me.cboSinger.Visible = Me.FKPartyType = 3
End Sub

I don't see any requery or whatever. Does this work?

Private Sub Form_Current()
  Me.cboArtist.Visible = Me.FKPartyType = 1
  Me.cboMusician.Visible = Me.FKPartyType = 2
  Me.cboSinger.Visible = Me.FKPartyType = 3
  Me.cboArtist.Requery
  Me.cboMusician.Requery
  Me.cboSinger.Requery
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

Thanks for responding. I like your suggestions.

That might help, but what if I am on 1 contract and Artist is chosen. Then I go to the next contract record. Artist is still chosen as the type for the next contract record, deal subrecord type, because maybe there is an existing record started and they already knew it was an artist, but had intentionally not filled in which one. Now when they go to choose an artist at this time, the combo is not requeried. Any way to catch that scenario?



misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

I am having trouble understanding your data. Is Type a bound combo box? What are the Row Sources of the other combo boxes? Can you provide the significant tables and fields?

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

Sure. Sorry about that.

Main form is frmContract

PKContractID (control source is tblContract for these fields)
txtContractName
otherfields


Subform Artist frmContractArtist

PKContractArtistID (control source is tblContractArtist)
FKContractID (lookup to the tblContract with parent child link on the main form to subform)
FKArtistID (lookup to tblArtist with PKArtistID, txtArtistName)
other fields

Subform Singer frmContractSinger

PKContractSingerID (control source is tblContractSinger)
FKContractID (lookup to the tblContract with parent child link on the main form to subform)
FKSingerID (lookup to tblSinger with PKSingerID, txtSingerName)
other fields

Subform Musician frmContractMusician

PKContractMusicianID (control source is tblContractMusician)
FKContractID (lookup to the tblContract with parent child link on the main form to subform)
FKMusicianID (lookup to tblMusician with PKMusicianID, txtMusicianName)
other fields


Subform Deals frmContractDeal

PKContractDealID (control source is tblContractDeal)
FKContractID (lookup to the tblContract with parent child link on the main form to subform)
FKContractPartyTypeID (lookup to tblPartyType with PKPartyTypeID and txtPartyType. The 3 values in this table are Artist, Singer and Musician. Those will never change)
FKSinger
FKArtist
FKMusician
other fields


When you go to the Deal subform (frmContractDeal) you have to have already set up the artists, singers and musicians that are in this contract. It is the flow of the data entry.

Once you go to this Deal subform, you choose the type and the choices are Artis, Singer or Musician. The code that I posted above makes the right combo visible. i.e. they choose Singer and FKSinger combo becomes available.

Each combo on frmContractDeal - FKSinger, FKArtist and FKMusician, has a row source with sql equivalent to this:

SELECT tblContractSinger.PKContractSingerID, tblSinger.txtSinger, tblContractSinger.FKContract
FROM tblContractSinger LEFT JOIN tblSinger ON tblContractSinger.FKSinger = tblSinger.PKSingerID
WHERE (((tblContractSinger.FKContract)=[Forms]![frmContract]![frmsubContractDeal].[Form]![FKContract]) AND (([Forms]![frmContract]![frmsubContractDeal].[Form]![FKPartyType])=3));

All 3 combos have the same control source to the tblContractDeal table field cboparty. The party type has a control source to FKPartyType in the tblContractDeal table as well.

I am kind of going in circles with this and not sure if I explained everything fully. Please let me know if this still needs more explanation.

Thanks!


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

I would suggest "circles" describes my feelings exactly. Why do you have separate table for artists, singers, and musicians? Does a contract feature only one type of party?

If your combo boxes aren't showing the correct information, then I expect you need to requery them.

I am just very confused by what should be a simpler table structure (IMO).

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

The reason they are separate is that an artist is never a singerand neither is a musician. They have different information to them.

In a contract, there are artists on that contract. Independently, singers are assigned to a contract and independently musicians are assigned to a contract.

Once those parties are set up, there are deals. There can be multiple deals for multiple parties in 1 contract.

So Contract A can have singer 1, 2 and 3, artist 4, 5 and 6, and musician 7, 8 and 9.

Then contract A can have a deal xyz with singer 1 and a deal ckl with artist 4 and deal opui with musician 7.

Go on to contract b and we might have singer 2, 27 and 29. So any party can be in any contract, but their information stems beyond contracts. Singers have a whole slew of other things captured about them. They are tied to assets, awards, work history and other stuff. Artists just have accounting information tied to them, and musicians don't really tie anything else to them.


I know it is really confusing, because this is a really complex project. I do promise that there is a reason the parties are kept separate. We don't store any of the same information about them. 1 gets full name and address, another just has a title and location tag, etc. They are horses of a different color which only have deals in common, because each of them can be assigned to a deal.

Make sense?

Thanks for continuing to try to help.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

I ended up getting this to work by putting the following in the next and previous clicks on the main form:

[frmsubForm].Form![FKControl].Requery

for each control.

Now they refresh like they are supposed to.

sweet!!

Thanks for all your help. I swear talking it through is what helps the most!

misscrf

It is never too late to become what you could have been ~ George Eliot