Refresh works for existing records but not New records

[penwood]

I have a mainform with 4 fields which are marked with 4 primary keys: soldtoId, shiptoID, PlantId and formulaID. PlantID is a long integer number field and the other 3 are text fields. there is no default values.

I have a subform1 that is linked by PlantID and FormulaID. These 2 field names are in the linked child fields.

I had a handful of records established already. Today, i added a command in the after update section of the PlantID field in mainform by adding Me.Refresh.
So when i change the plantID in the mainform, the subform gathers different plantID and formulaID data automatically. works nicely.

Here is the rub. I next tried adding NEW records to the mainform. that's the one with 4 primary fields.

i enter soldtoID and shiptoID fine. they have no refresh need. however, after the enter a PlantID, the me.refresh kicks in before i can get the the formulaID field. i get a debug error. something about a NULL. which i suspect is the formulaID that i didn't get a chance to enter to. FYI, later, i want to put a refresh on formulaID too.

so how can i make some kind of Refresh work for both New and existing records??
penwood

 

[penwood]

fyi - here are 5 methods that i unsucessfully tried so far.
penwood


===================================
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
====================================

 

[penwood]

Hi Pat - Here is what i have for your review.
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)

Yes, like i said, the mainform and subform are coordinated and do change automatically for existing records. that is not the problem.

what happens is, if i now try to add a brand NEW record in the mainform, i can enter the soldtoID, shiptoID and plantID. when i hit tab or enter to go to the formulaID field, that is when the debug starts and something about NULL.
i think this is when the plantID 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.

but then the mainform and subform will only stay in concert if i do not change the plantID. if i do, then the out of sync.

what do you think?
penwood

 

[penwood]

Pat - another illustration of how the necessary Requery is badly affecting my input mode.

let's say i am inputting a new record and i have the Requery in plantId. i do the 1st 2 fields and then put in a PlantID of say 4008. before i hit tab or enter, i suddenly realize that this record should be plant 4004. I try to change to 4004 with plans to hit tab or enter to the FormulaID. I pick 4004. But before i get the chance to hit tab to formulaID, i get the dreaded debug error message about Null.

I can't ask my inputter to never change a plantID input error until you finish the mainform 4 fields. i would think i can change each field input before i proceed to the next field. Do you see what is happening?

On a different thought, i went back to the mainform and took out the Requery in PlantID. got out and came back in. did a new input. fine as usual. the subform picks up the correct data. but when i change the plantID (without the requery in it), the subform never changes from the original state.
so one would think i need a requery in plantID.

but if i do that, then my new ADD's will have a problem like stated earlier whenever an inputter picks a plant 4008 and let's go of the combo choice and then change their mind for plant 4004. ergo, the NULL debug error because i did not reach the 4th field yet to input new.

ideas?

thanks
penwood in the woods

 

[penwood]

anyway, i took out the requery in PlantID.
temporarily, i just made a command button on the mainform. i used me.refresh inside the button's ON CLICK event procedure.
it works fine. Until we can fix the issue.

Which should i have used? refresh or requery? i am confused on this part.

this way, when doing a New mainform record, the inputter can change their initial input to plantID and continue down to formulaID. if needbe they can then hit my REFRESH button.

but i really wish i did not have to do this. still looking for a better fix.
penwood

 

[penwood]

I changed it to a Requery Button with me.requery inside the buttons ON Click event procedure. the refresh seemed to work too but from what i read, and what Pat said, i guess requery is the proper one.

but if you have ideas on how to imbed such a requery into the PlantID and yet allow the situation above to work, let me know.

that is, when adding a new record and when changing my mind on the desired PlantID while still in the new record add mode before i get to the formulaid, to allow me to change the plantID and not get a NULL debug error.
penwood

 

[penwood]

Pat - i am sure that you are right about perfect linkage. i just thought of something that i left out. i hope that i can explain this. or i can send a file if you like (tell me how).

in my subform sfrmdetail. it is based on a qryFormulaDetail which uses parts of 2 tables:

In this qry, i link tblFormulaDetail and tblRawItem using RawID. I use a line that says to include All records in tblFormulaDetail.

The main guy tblFormulaDetail has only 3 fields named FormulaID, RawID and usageAmount.

notice that tblFormulaDetail does NOT have PlantID which will probably surprise you. This is probably the beginning of the problem but i have no choice. i will explain shortly.

the tblRawItem has 3 fields: RawID, PlantID and cost.

Again, the 2 tables are linked by RawID.

So why don't i have PlantID in tblFormulaDetail too, you are wondering?

Because it is the SAME formula bill of materials regardless of PlantID.
I don't want a person typing in a formula detail for a different plant when the formula detail already exists. So i use One formula detail from tblFormulaDetail which ANY plant can use. (if i had plantId in tblFormulaMain and incorrectly forced my inputter to add a formula by plant, and if they make an error, then human health is at risk.)

Since a rawId-PlantID combination can have a different cost for EG Salt, then i need PlantID to get the correct cost from tblRawItem. And that is where PlantID from the mainform comes in via the link child fields thing.

does this explaination help?
penwood