Me.Refresh problem

[knuckelhead]

I almost have my PlantID problem licked.
I have a problem when i put a me.refresh in my PlantID field. Here is what I have.

For simplicity here, I have a main form with 4 fields: SoldtoID, ShiptoID, PlantID and FormulaID.

I cannot have a ME.refresh for the first 3 fields because i will get a NULL condition because formulaID has not been picked yet which is the 4th field. all 4 fields are combined in the table as Primary keys.

so how can i put a Me.Refresh on PlantID (field 3) and FormulaID (field 4) such that when entering a NEW record, i don't get a debug error due to the 4 primary key?
thanks
glen

 

[knuckelhead]

I did not mention that i have 4 subforms connected to this Mainform. the subform with formulaID and PlantID which are part of the Link child fields.

so when in the mainform, i change the PlantID, the subform is is supposed to change with it. but it will not change until i do a manual me.refresh.

For an existing record, if i put me.refresh into the mainform plantID, and change the plantID, everything is fine. so the problem has to do with NEW records not liking the Me.refresh.

glen

 

[knuckelhead]

I looked on the site for Refresh things. I tried one but it did not work. the subform does not change when i change the mainform plantID. I tried this in the mainform PlantID after update:

Private Sub cboPlantID_AfterUpdate()
Me![sfrmFormulaDetail].Form![PlantID].Requery
End Sub


As a reminder, a me.refresh works fine for existing records but not when i try to ADD a new record.

glen

 

[knuckelhead]

I just tried a 3rd method noted in this website. but it did not work.

[Forms]![frmJunction]![sfrmFormulaDetail].[Requery]


Private Sub cboPlantID_AfterUpdate()
'method 1 did not work - Me.Refresh
'method 2 did not work - Me![sfrmFormulaDetail].Form![PlantID].Requery

[Forms]![frmJunction]![sfrmFormulaDetail].[Requery]
End Sub

still looking for a way for a subform to change based on a mainform field, without having to manually hit the Records, Refresh menu. and still allow New input records to work with a refreshing method.
glen

 

[knuckelhead]

I guess this is a hard one for everybody. Anyone have ideas on how to bridge a subform to a mainform with automatic change but still allow New record entry?

all 3 methods that I found on the site so far do not work for me.

They only work on my existing records. when i enter a New record and having one of these techniques on the PlantID or FormulaID, the new input busts since all 4 fields on the mainform are primary keys.

eg i can entry soldtoID and shiptoID which do not have the refresh codes. but when i enter PlantID with a refresh after update, the input explodes with an NUll error message since i did not yet reach the FormulaID field.

But on existing records, which have all 4 primary fields filled out already, the refresh on PlantID or FormulaID work fine. Again, because all 4 primary fields already have something in them.

ideas??

thanks

 

[knuckelhead]

11-26-05 I tried a 5th method that i read about on the site. i checked my Allow Zero Length attributes in my table tblJunction. Then tried using Me.Refresh again. Still bombed out.

Private Sub cboPlantID_AfterUpdate()

'method 1 did not work - Me.Refresh
'method 2 did not work - Me![sfrmFormulaDetail].Form![PlantID].Requery
'method 3 did not work - [Forms]![frmJunction]![sfrmFormulaDetail].[Requery]
'method 4 did not work - Forms!frmJunction!sfrmFormulaDetail.Form.Refresh

'method 5 did not work - Checked and the long integer number field PlantID has a blank Default Value. good.
' I changed text fields soldtoID, ShiptoID, FormulaID ALLOW ZERO LENGTH to YES.
' then i put in a Me.Refresh. Tried a New input record. Bombed out with the me.refresh.


End Sub

any ideas??
thanks
glen

 

[formerTexan]

Hello Glen,


Somehow I think what you are describing is fundamentally more complicated than it needs to be, but I will take a try at what you have.

Just to cover the bases: are both the parent/child link fields set for the subforms? If so, refreshing of the subforms should be done automatically to track parent form record changes.

However, what you may try writing a function to check the status of all the primary key fields. Return true if all have values, false if any are missing. Then call this from the parent form's OnCurrent event and from the After UpDate event of each of the PrimaryKey fields. refresh the subforms if the function returns True.

CHeers,
Bill

 

[TheAceMan1]

How are ya knuckelhead . . .

In your post origination you said:

[blue] . . . all 4 fields are combined in the table as Primary keys.[/blue]

[blue]Are you talking a compound primarykey here?[/blue]

Be more specific about this . . . [blue]enough of your problem/s could easily emanate from here![/blue]

Also, [purple]what you've shown is indicitive of a problem with table structure & normalization[/purple] (at least to me), corrections of which could involve quite a bit of work.

Your thoughts?

See Ya! . . . . . .

 

[knuckelhead]

sunday 11-27-05 noon east
Bill
If you can solve this, i promise to pray for the Cowboys instead of my Giants.

Here is what i have for your review.
I have in my subform named sfrmFormulaDetail that is inside the mainform named frmJunction:

name: sfrmFormulaDetail
source object: sfrmFormulaDetail
link child fields: formulaID;PlantID
link master fields: FormulaID;PlantID

in my mainform called frmJunction, i have those 4 fields which travel back to tblJunction where i have these combined as 4 PRIMARY fields:
1 - name field cboSoldtoID (with soldtoId control source name)
2 - name field cboShiptoID (with shiptoId control source name)
3 - name field cboPlantID (with plantId control source name)
4 - name field cboFormulaID (with formulaID control source name)

The mainform and subform are coordinated and do change automatically for existing records. that is not the problem for existing records.

what happens is, if i now try to add a brand NEW record in the mainform, i can enter the soldtoID, shiptoID and plantID. but when i hit tab or enter to go to the formulaID field, the dreaded debug starts and something about NULL.

i think this is when the plantID field's After update Requery is kicking in before i get a chance to enter the formulaID.

so i had to take out the Requery in order to keep inputting.

Until real resolution, i made a Requery button with just Me.Requery. It is on the mainform. everything works fine but a person has to remember to hit the Requery button if they changed the PlantID.

So i now have zero Requery in the mainform primary fields but use a Button instead to do the dirty job.

I think you are onto something but i do not know VBA or the expressions that you have in mind.
glen

 

[knuckelhead]

hi aceman. i just saw your post after i responded to Bill.
Yes, all 4 fields are in tblJunction as a compound primary keys.
glen

 

[knuckelhead]

as a current summary:
my forms work in concert when i ADD a record and do not change the initial selected plantID. the subform is well coordinated. I no longer have any After Update Requery stuff. i use a button on the mainform to do that work should i ever change the plantID or formulaID. but i consider this a temporary stop gap method of mine.

I would like to avoid having a special Requery button.
When i had the Requery inside the PlantID's after update, here is what occured. During the ADD process, i get to the 3rd primary field and say i first pick say plantID 4008 and let go of the combo button, and bam there comes the debug Null issue because i had not had a chance yet to get to the 4th primary field formulaID.

glen

 

[knuckelhead]

i am terrible explaining this. i meant to say that i can currently change a PlantID and the mainform/subforms are coordinated as long as i hit the Requery Button. which i hope can be replaced with better code.
glen

 

[formerTexan]

Glen,

I think my earlier suggestion (checking to make sure all PK fields have valid values before refreshing) will get you headed in the right direction.

Of course you will need some mechanism to make sure the the PK is properly created before the user does anything else. I think that needs to be your first priority.

Cheers,
Bill