Help with subform requery?

[rhett7660]

Hi all..

I have two forms (one subform on the main form) and a detail form.

form number 1 is the main form (subform lists details based on a query)
form number 2 is the detail of the items in form #1

When I double click on a textbox SS# it opens up the details of that item in form number 2.

The subform has the following fields:
Id, SS#, Last Name, First Name, Middle Initial

ID=Auto
SS# is the box I click on to open the detail

The problem I am having is this. When I click on the SS# to enter a new incident, I fill out the full number hit tab to the next box (last name) then I use my mouse and double click on the SS# the form opens, when I click to make the record active (checkbox) I get the following error:

" You cannot add or change a record because a related record is required in table 'tblinvestigator' ". When I click Ok and then hit close form I get the following error: " You must save the current field before you run the Requery action. "



So I click ok and close the form. I then go back to the record in question fill out the first name, last name and then do a double click on the SS# and it works fine.

Is there anyway so I can update on just adding the SS# or do I need to fill out the four fields completely?






Thanks
R~

 

[boblarson]

Are you talking about the Investigator form where you can type in the subform? After I made the changes in your other posting I can type in just a SSN and then double click on it to open the details form. You do have to go out of the SSN field first because until you do it doesn't actually update the control. Once updated it adds the field so, you could add code in the change event of the text box to test for length and have it jump to the next field automatically so that the changes get committed and then you can double click on it.

 

[lightray]

The problem I am having is this. When I click on the SS# to enter a new incident, I fill out the full number hit tab to the next box (last name) then I use my mouse and double click on the SS# the form opens, when I click to make the record active (checkbox) I get the following error:

" You cannot add or change a record because a related record is required in table 'tblinvestigator' ". When I click Ok and then hit close form I get the following error: " You must save the current field before you run the Requery action. "

This is because the new SS# record hasn't been saved to the table yet! you can do it manually or code it into an event. But it should be done before you double-click to open the 2nd form.

 

[rhett7660]

Bob..

Yes the subform...

Ok what code would you add? so that once the SS# is entered it jumps to the next field?

Thanks again
R~

 

[boblarson]

I said that.

 

[lightray]

Try something like DoCmd.RunCommand acCmdSaveRecord before your open form 2 in the doubleclick event of the SS#.

 

[boblarson]

I'm working on it but I am finding a problem in that once you enter it and reach 11 characters, it doesn't let you go back to fix any.

 

[boblarson]

Lightray's solution is best and works great.

 

[rhett7660]

Edit.. Sorry my connection is super slow and when I am refreshing I am not getting some of the posts right off the bat.. I am trying Lightrays solution as we speak

R~

 

[lightray]

DoCmd.RunCommand acCmdSaveRecord See my post above!

 

[rhett7660]

It seems to be working.. Here is the code:

Private Sub txtSocNumber_DblClick(Cancel As Integer)
On Error GoTo Err_txtSocNumber_DblClick

DoCmd.RunCommand acCmdSaveRecord

    Dim stDocName As String
    Dim stLinkCriteria As String

        stDocName = "FrmApplicantFullDetails"
    
    If IsNull(Me.txtSocNumber) Or Me.txtSocNumber = "" Then
        MsgBox "This record is empty", vbInformation, "No Data"
        Me.txtSocNumber.SetFocus
    Else
        stLinkCriteria = "[ApplicantID]=" & Me![ApplicantID]
        DoCmd.OpenForm stDocName, , , stLinkCriteria
    End If

Exit_txtSocNumber_DblClick:
    Exit Sub

Err_txtSocNumber_DblClick:
    MsgBox Err.Description
    Resume Exit_txtSocNumber_DblClick
End Sub

Thanks again
R~

 

[lightray]

Just a note, you shouldn't need the IsNull test, as I expect you wouldn't be double clicking if the field was blank or do you think it might happen accidentally?

Cheers

 

[rhett7660]

Light..

Yup I think it will happen.... It has happened a couple of times already when I was showing another project to the end user....so now I put it in lol...

Thanks for your help!
R~

 

[ansentry]

Have a look at your db now.


Sorry I did not see the other replies before I posted, I have added the "DoCmd.RunCommand acCmdSaveRecord" as well.

Also I have disabled the controls in the subform when the Main form Primary key is Null. Also after you add or change data in the form frmApplicantFullDetails you don't have to save it just closing it will save the data.

 

[rhett7660]

ansentry

Thank you, I just downloaded it and I am looking at it.

Thank you again
R~