Getting defined VBA terms to work in an SQL query

[ClearlyAnIdiot]

Please note that I am a complete beginner in MS Access, and this may be impossible. I made a form(based on what I'll refer to as "Table 1") with a set of command buttons, and a some of them are meant to update existing records in another table (table 2), as well as table 1. The code is as follows:
Private Sub ExistingShareholder_Click()
Dim CompanyNo As Field

Dim ShareName As String
ShareName = InputBox _
(Prompt:="What is the shareholder's name?")


Dim Subroutine4 As String
Subroutine4 = "UPDATE table2 " & _
"Set ShareholderOf = 'CompanyNo' " & _
"WHERE Name = 'Sharename' "
DoCmd.RunSQL Subroutine4
End Sub


Basically, I want the Subroutine4 to recognise Sharename as the data I input to the inputbox, as well as the fact that CompanyNo is a field. If I put in some sample gibberish, such as

Subroutine4 = "UPDATE table2 " & _
"Set ShareholderOf = '1111' " & _
"WHERE Name = 'Person' "

it clearly recognises a field in table 2 that I'd named "person", and updates the "person" to be a shareholder of "1111". What I'm trying to ask is, how can I get the SQL to recognise what I've defined in the VBA?

 

[DavidAtWork]

try this:
Subroutine4 = "UPDATE table2 " & _
"Set [ShareholderOf] = ' " & Me.CompanyNo & " ' " & _
" WHERE [Name] = ' " & Sharename & " ' "
DoCmd.RunSQL Subroutine4

I'm assuming CompanyNo is a text field on your form with a string data type, hence the use of Me.CompanyNo and you therefore do not need to use the line:
Dim CompanyNo As Field
problems with running this kind of code usually stem from incorrect syntax wrapped around different variable data types

David

 

[ClearlyAnIdiot]

Nothing seems to have changed. Oh, and company number is a long integer field. Thanks for the advice, though! How does the concept of adding "&"s around the names work, exactly?

 

[DavidAtWork]

if CompanyNo is a number then this should work:Subroutine4 = "UPDATE table2 " & _"Set [ShareholderOf] = " & Me.CompanyNo & " " & _" WHERE [Name] = ' " & Sharename & " ' "DoCmd.RunSQL Subroutine4The &'s are standard syntax operators. If this still doesn't work, then I'd debug it by putting break in this code and step through it to examine the values inMe.CompanyNo and SharenameDavid

 

[ClearlyAnIdiot]

Umm, there doesn't seem to be any difference with the code this time and last time. I still end up updating none of the rows. I've tried with an without the first line "Dim CompanyNo as Field", messed around with the brackets, and done pretty much everything someone as clueless as me can do, but nothing's working, including the original code you gave me...

 

[pr2-eugin]

Try this..

Private Sub ExistingShareholder_Click()
    Dim Subroutine4 As String, ShareName As String
    
    ShareName = InputBox("What is the shareholder's name?")

    Subroutine4 = "UPDATE table2 SET [ShareholderOf] = " &[COLOR=Red][B] Me.CompanyNo[/B][/COLOR] & " WHERE [Name] = '" & Sharename & "';"
    DoCmd.RunSQL Subroutine4
End Sub

Is CompanyNo a Control/Field in the Form that you are using? If so the above code would work.. If not explain a little bit more..

PS: Please use Code Tags when posting VBA Code

 

[ClearlyAnIdiot]

That really worked. Thanks!