filling a form from a specific table

[ZeidHaddad]

filling a form from a specific table (Solved)

Hello Everyone! so i'm trying to pull data from a table and then update it in my form, not really sure if this is the best explanation :banghead: so to explain it better

Code:

Private Sub Befehl396_Click()
Dim rs As DAO.Recordset
    Dim StrSQL As String
    On Error GoTo cmdRefresh_Click_Err
    If Me.Vertragsnummer <> "" Then
        'commentary
        StrSQL = "SELECT *  " & _
                    " FROM Dokumentation WHERE Vertragsnummer = " & Me.Vertragsnummer & ""
        Set rs = CurrentDb.OpenRecordset(StrSQL)
        If Not (rs.EOF And rs.BOF) Then
            rs.MoveLast
            rs.MoveFirst
        End If
        
        Me.txtAnruf_1 = rs("Anruf_1")
        
        
        Me.Vertragsnummer.Enabled = False
        
        
        rs.Close
    Set rs = Nothing
    Else
        MsgBox ("Bitte eine Vertragsnummer wählen.")
    End If
    
    
cmdRefresh_Click_Exit:
    Exit Sub


cmdRefresh_Click_Err:
    MsgBox Error$
    Resume cmdRefresh_Click_Exit
End Sub

as You can see i'm trying to pull data from the table Dokumentation to my form. the problem it's not working and a message saying data conflict keeps appearing i'm already using another code for another button which is this one

Code:

Private Sub cmdRefresh_Click()
 Dim Vertragsnummer As String
Dim stlinkcriteria As String
Vertragsnummer = Nz(Me.Vertragsnummer.Value)
stlinkcriteria = "[Vertragsnummer] = " & "'" & Vertragsnummer & "'"
If Me.Vertragsnummer = DLookup("[Vertragsnummer]", "Dokumentation", stlinkcriteria) Then

MsgBox "Dokumentation der Vertragsnummer " & Vertragsnummer & " ist schon vorhanden!" _
& vbCr & vbCr & "Bitte in Dokumentation ändern.", vbInformation, "Dokumentation vorhanden"

'Me.Undo
End If
   
Dim rs As DAO.Recordset
    Dim StrSQL As String
    On Error GoTo cmdRefresh_Click_Err
    If Me.Vertragsnummer <> "" Then
        'commentary
        StrSQL = "SELECT *  " & _
                    " FROM EigenerBestand WHERE Vertragsnummer = " & Me.Vertragsnummer & ""
        Set rs = CurrentDb.OpenRecordset(StrSQL)
        If Not (rs.EOF And rs.BOF) Then
            rs.MoveLast
            rs.MoveFirst
        End If
        
        
        
        Me.txtName = rs("Name")
        Me.txtVorname = rs("Vorname")
        Me.txtTELEFONNR = rs("TELEFONNR_Kunde")
        Me.txtE_Mail = rs("E_Mail")
        Me.txtBausparsumme = rs("Bausparsumme")
        Me.txtAbschlussdatum = rs("Abschlussdatum")
        Me.txtSaldo_EUR = rs("Saldo_EUR")
        Me.txtSaldo_3112_Vorjahr_EUR = rs("Saldo_3112_Vorjahr_EUR")
        Me.TXTSollzins_Gebunden_Fest_JAEhrlic = rs("SOLLZINS_GEBUNDEN_FEST_JAEHRLIC")
        Me.txtGuthabenszins_Jaehrlich = rs("GUTHABENZINS_JAEHRLICH")
        Me.txtAnrede = rs("ANREDE_KUNDE")
        Me.txtGeburtsdatum_KUNDE = rs("GEBURTSDATUM_KUNDE")
        Me.txtEinwilligung_Werbung_Tel_W_W = rs("Einwilligung_Werbung_Tel_W&W")
        Me.txtTarifgeneration = rs("PRODNR_BEZEICHNUNG1")
        Me.txtTarif = rs("Tarif")
        Me.txtStrasse_Kunde = rs("STRASSE")
        Me.txtHNR_Kunde = rs("HNR")
        Me.txtPLZ_KUNDE = rs("PLZ5")
        Me.txtORT_KUNDE = rs("ort")
        Me.txtVL_Eingang = rs("VL_EINGANg")
        Me.txtDatum_Letzter = rs("Datum_letzter_Zahlungseingang")
        Me.txtZK_vorhanden = rs("ZK_vorhanden")
        Me.txtAbtretung_vorhanden = rs("Abtretung_vorhanden")
        Me.txtStruktur = rs("Strukturnummer")
        Me.Kundennummer_Wüstenrot = rs("Kundennummer")
        Me.txtTELEFONNR = rs("TELE_AUSPRAEGUNG")
        Me.txtAktion = rs("Aktion")
        Me.txtAktionsdatum = rs("AktionsDatum")
        Me.txtVD_Wuestenrot_Gebiet = rs("GEBIET")
        Me.txtVertreternr = rs("Vertreternummer")

        
        Me.Vertragsnummer.Enabled = False
        
        
        rs.Close
    Set rs = Nothing
    Else
        MsgBox ("Bitte eine Vertragsnummer wählen.")
    End If
    
    
cmdRefresh_Click_Exit:
    Exit Sub


cmdRefresh_Click_Err:
    MsgBox Error$
    Resume cmdRefresh_Click_Exit
    


End Sub

so maybe it's Affecting the first one ? not really sure

 

[vba_php]

the problem it's not working and a message saying data conflict keeps appearing

which line of code is throwing the error? your 2 code samples are behind 2 different buttons? if they are, then the two code routines HAVE to run at different times, so a conflict of interest between the 2 should not be a problem. one cannot click 2 buttons at the same time.

in both routines, you are pulling data from the first record of your table and putting it in your form controls. are you sure that the data type setting for all of your form controls matches the types that are being pulled from the table?

you're also not closing the recordsets in your code. perhaps you should? I have no idea if that matters, but programmers always do that anyway, plus they "set = nothing" at the end of the routine as well.

 

[ZeidHaddad]

which line of code is throwing the error? your 2 code samples are behind 2 different buttons? if they are, then the two code routines HAVE to run at different times, so a conflict of interest between the 2 should not be a problem. one cannot click 2 buttons at the same time.

in both routines, you are pulling data from the first record of your table and putting it in your form controls. are you sure that the data type setting for all of your form controls matches the types that are being pulled from the table?

you're also not closing the recordsets in your code. perhaps you should? I have no idea if that matters, but programmers always do that anyway, plus they "set = nothing" at the end of the routine as well.

Hello, thanks for your reply.
the thing is all data types are the same for sure! i don't think it's necessary to do the "set = Nothing" but thank you for the advice i will use it.

no line is being highlighted however after a test i did i think the problem is coming from here

StrSQL = "SELECT *  " & " FROM Dokumentation WHERE Vertragsnummer = " & Me.Vertragsnummer & ""
        Set rs = CurrentDb.OpenRecordset(StrSQL)

 

[vba_php]

i think the problem is coming from here

StrSQL = "SELECT *  " & " FROM Dokumentation WHERE Vertragsnummer = " & Me.Vertragsnummer & ""
        Set rs = CurrentDb.OpenRecordset(StrSQL)

what data type is "Me.Vertragsnummer"? if string, use:

"SELECT * FROM Dokumentation WHERE Vertragsnummer = '" & Me.Vertragsnummer & "'"

for numbers, use:

"SELECT * FROM Dokumentation WHERE Vertragsnummer = " & Me.Vertragsnummer

i'm jumping in the shower so I'll be away for about 20 minutes. hopefully it's fixed now if that line is truly the problem.

 

[Gasman]

What is the data type for Me.Vertragsnummer ?

Debug.Print strSQL and report back what it shows.

Numerics are not enclosed with anything.
Strings generally have ' unless there is a chance of that character being in the string, then use double double quotes instead. ""
Dates have #

I would have to ask though, why not just bind the form to the table?

HTH

 

[ZeidHaddad]

what data type is "Me.Vertragsnummer"? if string, use:

"SELECT * FROM Dokumentation WHERE Vertragsnummer = '" & Me.Vertragsnummer & "'"

for numbers, use:

"SELECT * FROM Dokumentation WHERE Vertragsnummer = " & Me.Vertragsnummer

i'm jumping in the shower so I'll be away for about 20 minutes. hopefully it's fixed now if that line is truly the problem.

Thank You adam it's working now! enjoy Your shower

 

[ZeidHaddad]

What is the data type for Me.Vertragsnummer ?

Debug.Print strSQL and report back what it shows.

Numerics are not enclosed with anything.
Strings generally have ' unless there is a chance of that character being in the string, then use double double quotes instead. ""
Dates have #

I would have to ask though, why not just bind the form to the table?

HTH

Hey it's because I'm using more than one table to add data into the Form
it's Solved now but thanks for Your Reply

 

[ZeidHaddad]

Oh Btw Is It possible to combine both codes under one button ?

 

[vba_php]

Oh Btw Is It possible to combine both codes under one button ?

of course it is. anything is possible when writing code (almost anything). but what you'll find a lot of times is that programmers will tell others that something is "impossible" because it's either "impractical" or the programmer doesn't want to be held liable for something that goes wrong when there are too many unknown variables in the scenario. so a response of "that's impossible" gets them off the hook immediately and the person asking the question drops the issue. I've run into countless scenarios of this happening. in the corporate world, it's a legal liability issue.

 

[ZeidHaddad]

of course it is. anything is possible when writing code (almost anything). but what you'll find a lot of times is that programmers will tell others that something is "impossible" because it's either "impractical" or the programmer doesn't want to be held liable for something that goes wrong when there are too many unknown variables in the scenario. so a response of "that's impossible" gets them off the hook immediately and the person asking the question drops the issue. I've run into countless scenarios of this happening. in the corporate world, it's a legal liability issue.

Okay i see! thank a lot adam i'll post another Thread asking how to combine both under 1 button only

 

[vba_php]

great!

 

[alaaaboulela]

gerat thanks to all