concatenate in VBA ACCESS

0
votes

How can i fix this ´UPDATE´ SQL query written in VBA? I want to update the ´Name´ field to the value from concatenating 2 fields in forms. I wrote this code but it doesn't work, any suggestions?

Here is my code:

strSQL = "UPDATE dbo.Soferi_test SET Name = '" & Me![subform_soferi].Form!numele
                                               & Me![subform_soferi].Form!prenumele & _
          "',Cipti = '" & Me![subform_soferi].Form!certificat_cipti & _
          "' WHERE IDNO = " & Me![subform_soferi].Form!IDNO
1
ms-accessvba
What exactly do you mean by "it doesn't work"? Does it cause an error, or does it just not do what you intend? – Gord Thompson
Do you want anything between the two fields? At the momen they are stuck straight together with no separator. You are missing & ";" at the end too. – Alistair Weir
they din't do any update when i click a button – Sergio
debug.print the sql and paste the output in your question – Alistair Weir

1 Answers

0
votes

Here's a basic outline of what I believe you are trying to achieve.

  • It looks to me like your form fields are referenced from a subform on a form.

Your sql looks very complicated when you put in a full reference to the objects, so i'd prefer to

  • create some variables
  • assign the form object to the variable and
  • reference the variable in the sql.

I think it makes the procedure more readable. You could call the below from a button on your form:

'****Update a table based on the values on a subform****'
'Table name:    dbo.Soferi_test
'Form Name:     frmMain
'Subform Name:  subform_soferi

Sub updateTable()

'Declare your variables which will be used in the sql string

Dim strSQL As String
Dim strNumele As String
Dim strPrenumele As String
Dim strCertificatCipti As String
Dim strIDNO As String

'Assign your variables to the form\subform objects
'You should also add some validation to check that the fields are not null

strNumele = Form_frmMain.subform_soferi!numele
strPrenumele = Form_frmMain.subform_soferi!prenumele
strCertificatCipti = Form_frmMain.subform_soferi!certificat_cipti
strIDNO = Form_frmMain.subform_soferi!IDNO

'Build your sql string
strSQL = "UPDATE dbo.Soferi_test SET Soferi_test.Name = '" & strNumele & strPrenumele & "', Cipti = '" & strCertificatCipti & "' WHERE (((Soferi_test.IDNO)='" & strIDNO & "'));"

'Execute the sql
CurrentDb.Execute strSQL

'You will want to put in some error handling here

End Sub