Search code examples
vbams-access

VBA code to provide Option to create additionalrecord

I have a DB that collects input via a Form "Abgabe_Unterlagen" into a Table "Abgabe_Unterlagen."Structure and Form are attached. The idea is to add records to the table "Abgabe_Unterlagen" so that the secretary can always identify which specialist has which records checked out.

As there are different types of records, it can happen that a customer turns in several different records. I use the option in below code, that if the record for that customer already exists in that table, a msg box informs accordingly and let the user create an additional record.

I'm looking for a solution that will a. use the value of the customer name already depict in the ComboBox "Mandantensuche" and copies it into the Field "Mandant_Name" in the Form.

b. It would be great if there could be a "Yes / No" option in the MsgBox to either to show and overwrite the existing record or to create an additional new one for this customer.

    Private Sub MandantenSuche_AfterUpdate()
   ' Den mit dem Steuerelement übereinstimmenden Datensatz suchen.     
     Me.Recordset.FindFirst "Mandant_Nummer_F = " & MandantenSuche
       If Me.Recordset.NoMatch Then
          MsgBox "Nichts gefunden. Bitte neu eingeben.", vbInformation
          DoCmd.GoToRecord , , acNewRec
          DoCmd.GoToControl "Mandant_Name"
     Else: MsgBox "Mandant gefunden, zusätzliche Akte eingeben...", vbInformation
          DoCmd.GoToRecord , , acNewRec
          DoCmd.GoToControl "Mandant_Name"
          'MandantName = MandantenSuche.Value
          'Mandant_Name.Value = MandantName
       End If
End Sub

Private Sub MandantenSuche_Enter()
    Me.MandantenSuche = Me.Mandant_Nummer
    'DoCmd.GoToRecord , "", acFirst
    End Sub

can anybody help to get me on track ?

v/r Banyan Table and Form

Solution

  • You can try something like this :

    Private Sub MandantenSuche_AfterUpdate()

    ' Den mit dem Steuerelement übereinstimmenden Datensatz suchen.
    Me.Recordset.FindFirst "Mandant_Nummer_F = " & MandantenSuche
    '
    If Me.Recordset.NoMatch Then
        MsgBox "Nichts gefunden. Bitte neu eingeben.", vbInformation
        DoCmd.GoToRecord , , acNewRec
        DoCmd.GoToControl "Mandant_Name"
    Else
        If vbYes = MsgBox("Neu eingebe ?", vbYesNo) Then
        
            DoCmd.GoToRecord , , acNewRec
            DoCmd.GoToControl "Mandant_Name"
            'MandantName = MandantenSuche.Value
            'Mandant_Name.Value = MandantName
        
        Else
            '
            'Do something else...
            '
        End If
    End If
    
End Sub