question on main/sub form record source

[JesOakley]

Hi guys, perhaps you'd help me out with this:

Form A has subform B. the properties of the control on A say that the link master is cboPart (a combo box on A) and the link child is Part (a key field on B).

The combo box looks up on the same table used in B, so B should always find records.

Some records on the table have a blank Part, but other data is populated. When I pick the blank from the combo box, no data is displayed in the subform.

I think I need to change the record source so that there are quotes around the space, but I don't know where the record source it is using is held.

If I cannot change the existing value, do I need to overwrite it with a statement of my own, and if so, where?

As always, I appreciate your responses.

 

[TheAceMan1]

How are ya JesOakley . . .

Why not just exclude blank parts from cboPart? . . .

Post the rowsource of cboPart so we can have a look . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[JesOakley]

I hear you Aceman, but I don't want to exclude the records with blank parts. In fact I can imagine my users wanting to find precisely those records.

Using 'explicit' on the row source means I only see one blank in the combo box, which is good, but I need the subform to pick up the matching records.

I feel sure that it's only a question of getting a few quotes around the 'space' value in whatever select statement is being applied, but I just don't know how to change it.

Any further thoughts would be appreciated.

 

[formerTexan]

Do you have a primary/foreign key relationship between the 2 sets of records?

 

[TheAceMan1]

JesOakley . . .

I hear and understand you! Its programming practice that causes me to raise a red flag.

If you could explain, [blue]the point of clicking on a part you know not?[/blue]

In any case, if you want to rocket this thread, we are code people, it explains all! Do post the recordsource for [blue]cboPart[/blue] . . . and don't forget to answer [blue]formerTexans[/blue] post . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[JesOakley]

Okay.
The table in question is a PartSuppliers file. It's really only one set of records as both the combobox and the the subform use the same table. Primary key is PartNo/SupplierCode, alternate keys are SupplierCode and SupplierPartNo. Other stuff on table includes price etc etc. The blank key, the one used by the combo box is SupplierPartNo. I don't know why they're not filled in in the first place, but this is archive data going back years, and as such must remain inviolate.
Clicking on a blank SupplierPartNo in the combo box should list all the PartNos that don't have a SupplierPartNo, along with their prices etc. The SupplierPartNo may then be updated in other systems.
Record source for the combo box is 'SELECT distinct [tblPartSuppliers].[SuppPartNo] FROM [tblPartSuppliers] ORDER BY [SuppPartNo];'

I appreciate that maybe i'm not the best coder in the world, and I am grateful for your advice. My primary question here though is how to change the link between the form and the subform so that a blank value can be passed inside quotes, because I'm pretty sure that's all it will take.

Cheers guys

 

[JoeAtWork]

Probably the blank record has a Null value in the field. Nulls don't equal each other, for example the following does not return True:

Null = Null

You need to translate the Null value into something that can be compared. For example:

Nz([tblPartSuppliers].[SuppPartNo],"") AS SupplierPartNo

The Nz function will transform the value to "" if it is Null.

Do the same in the subform's recordsource. Then it will be able to compare:

"" = ""

 

[JesOakley]

Hi Joe,
Yes, a quick query confirmed that the 'blank' fields were in fact null. Now this is where I get confused! I can apply the nz you suggested in the subform, but then I guess I'll have to change the Subform Control's Link Child Field to use SupplierPartNo instead of SuppPartNo. Thing is tho, SuppPartNo was an index, asking it to match against my new SupplierPartNo probably loses the benefit of the key, and on 400,000 records, I'm not sure I can afford that!
Also, I'm not quite sure where I would change the combo box; the rowsource for the combo box is 'SELECT distinct tblPartSuppliers.SuppPartNo FROM [tblPartSuppliers]'. If I try to make it 'SELECT distinct nz(tblPartSuppliers.SuppPartNo,"") as SupplierPartNo FROM [tblPartSuppliers]' and run the form, then some strange things happen: (a) the combo box shows the 'blank' as usual (being top of the list) and (b) the subform shows all the nulls (!). (c) If I then choose another value from the combo box, and then back up to the blank one again, it then displays no records in the subform. (d) when I look at the rowsource for the combo box again the nz'd code has reverted back to the non-nz'd version! Weird or what? I should mention that I have '=[cboSuppPartNo].[ItemData](0)' in the combo box's default value.
I'm now so confused that I'm thinking about giving up on it!

 

[JesOakley]

Not to worry... Decided I'd do it all in a single continuous form; far easier, if not as pretty!