not in list, create further record

[Delid4ve]

I have a sub form to which the tables have referential integrity checked.

There are two PKs in this sub form that i need to update on the fly.

the relationship is as follows:

Refrigeranttype,RefrigerantID(pk) 1-~ Cylinderserialno(pk), RefrigerantID(Fk), Tareweight, netqty

When i add a new cylinder on my subform at present i get a runtime error as it requires a record in the refrigerant table to to RefInteg

Could somebody help me extend the code so that on adding a new cylinder it immediately asks to pick the refrigerant that the cylinder contains and inturn holds up on ref integ without error. It would be helpful if this can pull up a combo box if possible as its a predefined list.

Code at present:
Private Sub Combo8_NotInList(NewData As String, Response As Integer)
'Get confirmation that this is not just a spelling error.
StrTmp = "Add '" & NewData & "' As a new cylinder?"
If MsgBox(StrTmp, vbYesNo + vbDefaultButton2 + vbQuestion, "Not in list") = vbYes Then

'Append the NewData as a record in the Categories table.
StrTmp = "INSERT INTO Cylinders ( CylinderSerialNo ) " & _
"SELECT """ & NewData & """ AS CylinderSerialNo;"
DBEngine(0)(0).Execute StrTmp, dbFailOnError

'Notify Access about the new record, so it requeries the combo.
Response = acDataErrAdded

 

[James Deckert]

if you make the form the way it would normally be created you'll have the Refrigerant info in the master form and the cylinder info in the subform. If this is the way you have it set up, then the Refrigerant ID field should be filled in automatically to match the Refrigerant displaying on the master form. If this is the way you have it set up, but it's not working, then fix the problem, instead of coding a fix. Check the 'Link Master Fields' and 'Link Child Fields' in the subform.
If this is not the way the form is set up, please explain how it's set up.

 

[Delid4ve]

Hi James

Thanks for the response.

Ive worked around this issue by rearranging how the data is stored. However, how would i go about this as im sure its going to crop up once i add more data and have to split the tables again:

RefrigerantTable: RefrigerantID(PK),RefrigerantType (at present ive de normalised and just put refrigerant as a set lookup list within the cylinder table, as im not ready to add more data yet but will be further fields specific to the refrigerant so a seperate table will be needed)

1
many

CylinderTable: CylinderSerial(PK), RefrigerantID(FK), NetQty, TareWeight etc etc

many2many with join table (CylinderSerial(PK),DocumentID(PK)

DocumentTable: DocumentID(PK),DocumentType,CollectionDate,ReturnDate etc etc

The cylinderserial, refrigerantID, TareWeight,Netqty is a subform within the document form. The problem comes when i add a not in list cylinder and then throws the error as no refrigerant is selected.

Im still learning and dont understand the child/master field clear enough atm. If you have a simple explanation involving the tables above then it will help me understand.

Thanks

 

[James Deckert]

If you go through the Form Design Wizard, it'll build the master/child automatically. They only need to be modified if something changes or is broken.