Saving record on subform

[Jgrant@soilsafe.com]

I have a subform connected to a main form. The subform is a tab form with a one to many relationship. The subform directs data from one of the fields in my subform into another hidden field in the record. it is based on an if then statement based on a field in my main form. THe problem is that the code will not recognise the information in the subform until I leave the main form (or it loses focus) and come back. Then all my code works fine. I've tried DoCmd.RunCommand. AcCommandRecordSave and that didn't work.

This is the code I have so far. It just won't work and I can't figure it out. Pls Help

Dim db As DAO.Database
Dim rst As Recordset
Dim strSearchName As String
Dim AppnoSSI As String
Dim AppNoCEC As String
Dim AppNoSSIM As String

Set rs = Me.Recordset
rs.Edit

DoCmd.RunCommand acCmdSaveRecord




If Forms!approvalnumbers!Facility = "SSIL" Then
[appNoSSIL] = [Forms]![approvalnumbers]![ApprovalLetterInfosubfrm].Form![ApprovalLetter]

 

[Jgrant@soilsafe.com]

I have 17 if then statements in the code for various options (facilities) I think the recordset code is left over from trying other things that didn't work. The data is being input in a subform and the if then statements are based on the answer to a question on the main form. What I was trying to do is to get the record to "save" in a table, so the value of the field is defined and then place that into one of 17 additional fields.

For example I have a field "Facility" in my main form which is a drop down box that feeds a field in the parent table. for ease of data entry I didn't want to put 17 boxes in my subform so I put a text box "AppLet" and then wrote the code behind the button. If Facilities = "A" then the data is pushed to field 3. If Facilites = "B" then the data is pushed to field 4 I just can't get the button to recognize the data entered in the AppLet text box until i leave the form and come back in. Then everything runs fine.

Access isn't my primary skill if you can even call it a skill of mine. Thank you so much for the quick reply.

 

[Jgrant@soilsafe.com]

Pat,

I did notice that you pointed out I was saving the record before manipulating the data. If I don't save the record and the record is still dirty (I tried the If me.dirty then me.dirty=false routine and it didn't work) then how will access know the value of the applet field?

I'm not sure what you mean by avoiding putting a combo box on each form. I have one combo box on the main form where the user selects one of 17 Facilities. If I was to require data entry in the appropriate facility applet field then I would have to have 17 text boxes right? I don't where else to put the if then statements. Or is there a way to make a combo box drop data into a specific field (collumn) of a recordset based on the selection. For example can I take a combo box populated by a list from a table and have it assign data to another table based on the selection?