Form field not populating through to table

[RCheesley]

Hi all,

I've got a setup with a Master form:

frmPatientDetails

And a subform:

Specimens

which is tabular, based on a form "frmSpecimenDetails" which runs from a query "qrySpecimenDetails"

On the subform for each specimen there is a button (cmdAddAdmission) which opens the form "frmAdmissions" to allow the user to add an admission related to that particular specimen. There could be several admissions for each specimen.

When the form "frmAdmissions" opens I have used the following control sources to populate the fields of SpecimenID and NHSNumber:

=Forms!frmPatientDetails!Specimens.Form!SpecimenID
=Forms!frmPatientDetails!PatientDetailsNHSNumber

While the fields are being populated perfectly, they are not being entered into the table ("0" is being inserted).

I'm fairly sure this is because it's just pulling the data in from the other form, and hence it's not a real "value" in the box. Any ideas how to resolve this?

Ruth

 

[rainman89]

Do you have the forms linked?

 

[RCheesley]

Yep, the subform is linked to the main form via the PatientID & NHS Number

 

[rainman89]

I had something similar to this where I had to create a unbound textbox on the subform to make sure I was getting the correct value using that unbound textbox set the ID value using an event on my save button. It was weird, but it was the only way that I could get it to work correctly.

 

[RCheesley]

Ray,

Sorry could you clarify that a bit for me, as I'm somewhat confoozled by what you mean! Put an unbound text box I got as far as, but no further!! :$

 

[rainman89]

Before we jump into that, It seems as I have misread your problem. I thought It was a problem with a sub form, but now see it is a totally separate form.

So we go away from that.

You say that everything is being populated correctly? but not being saved? You have tried creating a save command correct?

You could try putting

SpecimenID =Forms!frmPatientDetails!Specimens.Form!SpecimenID
PatientDteailsNHSNumber =Forms!frmPatientDetails!PatientDetailsNHSNumber

In a save button or something Instead of relying on the fact that it is a bound field

 

[RCheesley]

Hi Ray,

Would I put that somewhere in the VBA for the cmd button? Yes I do have a "Save Record" button but had not thought to add this to it.

Private Sub cmdSaveRecord_Click()
On Error GoTo Err_cmdSaveRecord_Click


    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_cmdSaveRecord_Click:
    Exit Sub

Err_cmdSaveRecord_Click:
    MsgBox Err.Description
    Resume Exit_cmdSaveRecord_Click
    
End Sub

 

[rainman89]

Yeah you could put it right above the save statement

 

[RCheesley]

Guess I'm doing something wrong, I get "Overflow" error popup?

 

[RCheesley]

Ahar, n00bie points for me ... the field in the table wasn't large enough *hides in a corner*

 

[rainman89]

So did it work then?

 

[RCheesley]

Nope. Still not saving the data. I tried changing it to PatientDetailsID rather than NHS Number to simplify things (patientdetailsID is an autonumber & PK for PatientDetails table).

Here's the code:

Private Sub cmdSaveRecord_Click()
On Error GoTo Err_cmdSaveRecord_Click

Dim SpecimenID As Integer
Dim PatientID As Integer
SpecimenID = Forms!frmPatientDetails!Specimens.Form!SpecimenID
PatientID = Forms!frmPatientDetails!PatientDetailsID

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_cmdSaveRecord_Click:
    Exit Sub

Err_cmdSaveRecord_Click:
    MsgBox Err.Description
    Resume Exit_cmdSaveRecord_Click
    
End Sub

 

[rainman89]

Nope. Still not saving the data. I tried changing it to PatientDetailsID rather than NHS Number to simplify things (patientdetailsID is an autonumber & PK for PatientDetails table).

Here's the code:

Private Sub cmdSaveRecord_Click()
On Error GoTo Err_cmdSaveRecord_Click

Dim SpecimenID As Integer
Dim PatientID As Integer
SpecimenID = Forms!frmPatientDetails!Specimens.Form!SpecimenID
PatientID = Forms!frmPatientDetails!PatientDetailsID

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_cmdSaveRecord_Click:
    Exit Sub

Err_cmdSaveRecord_Click:
    MsgBox Err.Description
    Resume Exit_cmdSaveRecord_Click
    
End Sub

You shouldnt need to declare them as integers.

They should be the fieldnames of the boxes on your form!

 

[RCheesley]

I use options explicit and it throws an error without the declarations.

The fields of SpecimenID and PatientDetailsID on the form where the data is entered are named as such, however their control sources are as I posted above - I'm not sure if this is what you're referring to?

 

[RCheesley]

Ahar I figured it out!!! It doesn't quite work as intended as it doesn't show the values when the form appears but it does populate them through to the table:

Private Sub cmdSaveRecord_Click()
On Error GoTo Err_cmdSaveRecord_Click

Me.SpecimenID.Value = Forms!frmPatientDetails!Specimens.Form!SpecimenID
Me.PatientDetailsID.Value = Forms!frmPatientDetails!PatientDetailsID

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_cmdSaveRecord_Click:
    Exit Sub

Err_cmdSaveRecord_Click:
    MsgBox Err.Description
    Resume Exit_cmdSaveRecord_Click
    
End Sub

I got rid of the control sources I mentioned earlier

=Forms!frmPatientDetails!Specimens.Form!SpecimenID
=Forms!frmPatientDetails!PatientDetailsNHSNumber

and it worked

However, is there a way to display the figures before clicking the save button? I thought about maybe on form load but it didn't seem to work.

Ruth

 

[rainman89]

If you really want to see them, What if you just make a plain textbox to show them and use your control sources for them?

 

[RCheesley]

Ahar, super duper Working brilliantly! I actually feel like I've accomplished something today, many thanks for your help