Not Adding a new record (1 Viewer)

[ivonsurf123]

Hello,


Hopefully someone can help me with this problem, I an trying to add data in two fields on a subform call "B" from a subfrom call "A", but it 's no creating an "New" on ID field when I filter the data to be add it in subform "B" Please, help. Thank you.

Private Sub cboGoToPosition_AfterUpdate()
On Error GoTo ErrHandler:

    Dim str As String
    str = cboGoToPosition.SelText
    


   Me.RecordSource = "SELECT tbl_GCDS_Operations_Positions_Recruit.* " & _
        "From tbl_GCDS_Operations_Positions_Recruit " & _
        "WHERE (((tbl_GCDS_Operations_Positions_Recruit.[Position Applied For])='" & Me.cboGoToPosition.Column(0, Me.cboGoToPosition.ListIndex) & "*" & Me.cboGoToPosition.Column(1, Me.cboGoToPosition.ListIndex) & "*')) " & _
        "ORDER BY tbl_GCDS_Operations_Positions_Recruit.[Position Applied For];"
    'Debug.Print Me.RecordSource
   Me.Requery
    
 
        Call cmdAdd_Click
 
Exit Sub
ErrHandler:
    MsgBox Err.Description
End Sub

Private Sub cmdAdd_Click()
 On Error GoTo ErrHandler
 
    Dim str As String
    str = Me.cboGoToPosition
    Dim dbTemp As Database
    Dim rsTemp As Recordset

    'Open connection to current Access database and perform the search
    Set dbTemp = CurrentDb()
    Set rsTemp = dbTemp.OpenRecordset("SELECT [REPLACEMENT FOR],[Position Name] From tbl_GCDS_Operations_Positions_Fills " _
                                      & "WHERE [REPLACEMENT FOR]= '" & str & "' and [Position Status] = 'Open'")

   Me.RecordSource = "SELECT * " & _
       "From tbl_GCDS_Operations_Positions_Recruit " & _
       "WHERE ((([Position Applied For])='" & Me.cboGoToPosition.Column(0, Me.cboGoToPosition.ListIndex) & "')) " & _
       "ORDER BY[Position Applied For];"
    Me.Requery
   
    'Update fields if data is found

    If rsTemp.EOF = False Then
       'rsTemp.AddNew
       Me.Replacement_For = rsTemp![REPLACEMENT FOR]
       Me.Position_Applied_For = rsTemp![POSITION NAME]
       'rsTemp.Update
    Else
       Me.Replacement_For = ""
       Me.Position_Applied_For = ""

    End If
    
    rsTemp.Close
    Set rsTemp = Nothing
    Set dbTemp = Nothing
    
    Exit Sub
ErrHandler:
    MsgBox "Couldn't create record", vbCritical
End Sub

 

[isladogs]

If you run a debug line after me.recordsource you will see at least one error.
Missing space after ORDER BY

There may be other issues.

It will help troubleshooting if you change your error handing code to something like

MsgBox "Error " & err.number & " " & err.description & " in (put proc name here)"

 

[ivonsurf123]

Hello,


Thank you for your input, run it but that's not the problem, it's just no adding a new ID for the New record, that's why cannot save it.


This is the result:

SELECT tbl_GCDS_Operations_Positions_Recruit.* From tbl_GCDS_Operations_Positions_Recruit WHERE (((tbl_GCDS_Operations_Positions_Recruit.[Position Applied For])='Lewis, Richard (GB)*Data Analyst*')) ORDER BY tbl_GCDS_Operations_Positions_Recruit.[Position Applied For];

but in here (Me.ID) I see a Null ID when a point the mouse on top:


[Forms]![Navigation Form]![NavigationSubform].Form!txtID = Me.ID

 

[isladogs]

I was referring to the code in cmdAdd_Click when I said there was a missing space

 

[ivonsurf123]

Hello,


Did the same thing, same results:

SELECT tbl_GCDS_Operations_Positions_Recruit.* From tbl_GCDS_Operations_Positions_Recruit WHERE (((tbl_GCDS_Operations_Positions_Recruit.[Position Applied For])='Lewis, Richard (GB)*Data Analyst*')) ORDER BY tbl_GCDS_Operations_Positions_Recruit.[Position Applied For];

But still is not adding a new ID on the ID Field that's why cannot save it.

 

[Gasman]

Would that be due to to AddNew and Update being commented out?

 

[ivonsurf123]

No, On Private Sub Form Current() I had:

Me.lblRecordCounter.Caption = "Record " & Me.CurrentRecord & " of " & Me.Recordset.Recordcount

I changed that for:

 If Me.NewRecord = False Then
         Me.lblRecordCounter.Caption = "Record " & Me.CurrentRecord & " of " & Me.Recordset.RecordCount
end if

It worked.

 

[isladogs]

Glad you got it working but as you didn't post that part of your code, there's no way we could have known that!