DOA Function breaking when changing form record

[IllusionRay]

hello guys, im working on a form that allow the user to attach a bill to bill list of a specific provider and for that i am using a DOA function to edit a value in the bill table when adding or removing it. it work fine if i open the form and add or remove on the actual record but if i change the record using the upper right list box, thos 2 button that use the 2 DOA function wont work after the record changed. i dont have much time to waste finding the problem i gona need that form working for next week and since im not a expert in coding and debug i am looking for help here to save some time figuring it out.

thx a lot for your time guys!

i uploaded the db

haaa btw the 2 DOA function are in the EtasDeCompte Module moved them there for testing but dint help at all.

 

[IllusionRay]

Ok the upper right list was broken it was not returning the right value, i deleted it and made a new one and now its returning the right value but the Add and Remove buton keep breaking at some point it not entering the function condition and not executing the loop. im still trying ti figure out why.

 

[Mark_]

Please post the code and what error(s) you are receiving.

 

[IllusionRay]

if you download the DB i uploaded you will have all the code i use, i dont get any error nothing happen when clicking the add or remove button after changing record.

this is the add button code:

Function AjoutFactureEc()

        Dim dbs As DAO.Database
        Dim rst As DAO.Recordset

        Set dbs = CurrentDb
        Set rst = dbs.OpenRecordset("Tbl_Facturation")
        Forms("Frm_Etas_De_Compte")("Ec_List_Facture").SetFocus

        rst.MoveFirst
        Do Until rst.EOF
            If rst!ID = Forms("Frm_Etas_De_Compte")("Ec_List_Facture") Then
            rst.Edit
            rst!Ec_ID = Forms("Frm_Etas_De_Compte")("Ec_Txtb_ID")
            rst.Update
        End If
        rst.MoveNext
        Loop

End Function

this is the remove button code:

Function EnleveFactureEc()

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Tbl_Facturation")
Forms("Frm_Etas_De_Compte")("Ec_List_Ec_FactureLier").SetFocus

rst.MoveFirst
Do Until rst.EOF
If rst!ID = Forms("Frm_Etas_De_Compte")("Ec_List_Ec_FactureLier") Then
rst.Edit
rst!Ec_ID = 0
rst.Update
End If
rst.MoveNext
Loop

End Function

and this is the upper right list box that select the record to be showed on the form:

Private Sub Ec_List_EtasdeCompte_Click()
        
        Dim RecordLookUp As Integer
    
        Me![Ec_List_EtasdeCompte].SetFocus
        RecordLookUp = Me![Ec_List_EtasdeCompte].Value
        Me.Filter = "[Ec_ID] = " & RecordLookUp
        Me.FilterOn = True
        Me.Refresh
        
End Sub

 

[IllusionRay]

trying to use sql to replace the DAO function to see if i got the same issue but i cant find the right syntax i got: missing parameter, expecting 1 error on this line:

CurrentDb.Execute "UPDATE Tbl_Facturation " & "set [Ec_ID] = 0 " & "where ID = Ec_List_Ec_FactureLier.Value;"

some advice would be welcome ill keep looking around to see if i can make it work.

 

[Cronk]

For a start your SQL should be

CurrentDb.Execute "UPDATE Tbl_Facturation " & "set [Ec_ID] = 0 " & "where ID = " & Ec_List_Ec_FactureLier.Value;

This assumes ID is numeric.

Or maybe

CurrentDb.Execute "UPDATE Tbl_Facturation " & "set [Ec_ID] = 0 " & "where ID = " & me.Ec_List_Ec_FactureLier.Value;

Incidentally, you can leave off the .Value bit because that is the default.

 

[IllusionRay]

i had to remove the ; at the end to make it work

CurrentDb.Execute "UPDATE Tbl_Facturation " & "set [Ec_ID] = 0 " & "where ID = " & Me.Ec_List_Ec_FactureLier.Value

work fine and using sql actualy solved my problem! yay

now im trying to make this to work:

CurrentDb.Execute "UPDATE Tbl_Facturation " & "set [Ec_ID] = Me.Ec_Txtb_ID.Value " & "where ID = " & Me.Ec_List_Facture.Value

but like that im getting the: Too few parameter. Expected 1. Error

if i single quote: 'Me.Ec_Txtb_ID.Value' the error is gone but it return a null value

 

[IllusionRay]

Look like that worked:

CurrentDb.Execute "UPDATE Tbl_Facturation " & "set [Ec_ID] = (" & Me.Ec_Txtb_ID.Value & ") " & "where ID = " & Me.Ec_List_Facture.Value

SQL have a really weird syntax... first time using it, well thx Cronk you gived me the last push i needed to figure this out

i still wonder why my DAO function bug if i switch record tho that is also really weird...

 

[Mark_]

Dim asSQL as STRING
asSQL = "UPDATE Tbl_Facturation set [Ec_ID] = " & Me.Ec_Txtb_ID & " where ID = " & Me.Ec_List_Facture
msgbox "SQL is " & asSQL
CurrentDb.Execute asSQL

Check exactly what you will be sending. For myself, this is a habit to get into as you will avoid MANY issues if you always check what your SQL string will be first.

In your original command you were putting quotes in the wrong places and you are referencing a field on your screen within the SQL string rather than referencing the value.

 

[IllusionRay]

Thx to every one who spend time on this its really appreciated, if any one figure out why the DAO is buging out if the record is changed let me know, thx again

 

[IllusionRay]

yeah problem was i dint know how the string should looked like since i never used sql be for and even now im still not sure lol, but yeah ill do this next time i going to use SQL at least i will see how it look like and it will help me learn how it should look like, thx for the tip Mark.

 

[Mark_]

Ray,

Just start doing searches on the different parts of the SQL statement. You can figure most of it out from examples you will find online.

This link gives a good example of what you are doing.

 

[Cronk]

Better still, design your query in the Access query GUI, then look at the generated SQL by selecting View | SQL