Results 1 to 15 of 15
  1. #1
    bogdan1245 is offline Novice
    Windows 7 32bit Access 2000
    Join Date
    Apr 2011
    Posts
    8

    How to Reference a text box in VBA

    Hello!



    I'm new to this forum, so hopefully what I'm writing will make sense to you...

    I'm working on a library database. One of my forms inputs the following parameters:

    BorrowerID - to reference borrower
    RecordID - to reference book

    I also want to set Binary Value for that book as False to indicate that it's been taken. The name of the binary column is "Available"

    All these variables are part of the Query "Request_Q" on which the form is based.

    So, to set the value to false I go into properties of a command button that saves the form's record, and in the Event->OnClick I go into code builder.

    Here is what I write:


    Private Sub Command8_Click()

    Dim strSQL As String
    strSQL = "UPDATE Books_By_Library SET Available = False WHERE RecordID = Combo4"


    Books_By_Library is the name of the table where "Available" column is located.
    Combo4 is the name of the textbox in the form that receives RecordID number.

    So, I'm pretty sure that the SQL command is correct, however I don't know how to correctly reference that Combo4 textbox.

    Any help would be greatly appreciated!

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,640
    This should help:

    Forms Refer to Form and Subform properties and controls

    but you have to concatenate the value:

    strSQL = "UPDATE Books_By_Library SET Available = False WHERE RecordID = " & Me.Combo4"
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    bogdan1245 is offline Novice
    Windows 7 32bit Access 2000
    Join Date
    Apr 2011
    Posts
    8
    Thank you for the prompt reply!

    Somehow it doesn't work.

    I even got rid of the variable and tried to use this expression but it didn't update the record:

    strSQL = "UPDATE Books_By_Library SET Available = False WHERE RecordID = 5"

    In theory, I can fix the problem by just adding another box for available and uncheck it every time I make request, but I want it to be done automatically. Is there any other way to do it?

    Thank you!

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,640
    How are you executing the SQL? Do you get an error? What is the data type of the RecordID field?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    bogdan1245 is offline Novice
    Windows 7 32bit Access 2000
    Join Date
    Apr 2011
    Posts
    8
    I have a form and a control button that saves the record. So, what I did was to go into properties and in the On Click I go into code builder and insert that line. That's all I did. RecordID is AutoNumber (Long Integer). No, I don't get any errors. The form executes correctly, it just doesn't update Available field

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,640
    That just populates a string variable; you have to execute it. Add this line after that one:

    CurrentDb.Execute strSQL, dbFailOnError
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    bogdan1245 is offline Novice
    Windows 7 32bit Access 2000
    Join Date
    Apr 2011
    Posts
    8
    It worked! Thank you so much! At least now I have an idea how VBA works. I just never programmed on it before.

  8. #8
    bogdan1245 is offline Novice
    Windows 7 32bit Access 2000
    Join Date
    Apr 2011
    Posts
    8
    Another question: This button that I use was created using wizard under "Save Record". Is it possible to write code to stop it from saving record or I have to create an unbound button for that? If there is a code, what would it be?

    Thank you so much for your help!

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,640
    Happy to help! If the form is bound, it's going to try and save anyway; you could stop it in the before update event:

    http://www.baldyweb.com/BeforeUpdate.htm

    If the form is not bound, you'd just need to adjust the code behind your button.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    bogdan1245 is offline Novice
    Windows 7 32bit Access 2000
    Join Date
    Apr 2011
    Posts
    8
    Thanks!

    I inserted the following code in the Before Update:

    Dim strSQL1 As String

    strSQL1 = "SELECT Available FROM Books_By_Library WHERE RecordID = " & Me.Combo4


    If strSQL1 = False Then
    MsgBox "This book has already been taken by another Borrower."
    Cancel = True
    End If



    However, the debugger tells me Run-Time Error 13. Type mismatch. What is it?

    P.S. I tried to set strSQL1 as Integer, but got the same problem. Technically, I'm trying to extract binary info from available, but the SQL command is in itself a string, so I'm not sure which type to use.

    Thank you!

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,640
    This is the same issue; the SQL string is simply a string variable. You have to execute it in some way to get the resulting value. In this case, you would open a recordset on your SQL string to get the result, but you might find a DLookup easier.

    If DLookup(...) = False Then

    More info on the syntax here:

    DLookup Usage Samples
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    bogdan1245 is offline Novice
    Windows 7 32bit Access 2000
    Join Date
    Apr 2011
    Posts
    8
    Thank you so much for your help!

    Can you please tell me what's wrong with this code:

    ---------------------------------------------
    Dim strSQL As String
    Dim strSQL1 As String
    Dim nSQL As String

    strSQL1 = "UPDATE Request SET Return_Date = ' " & Me.Text36 & " ' WHERE RequestID = " & Me.Combo34

    nSQL = DLookup("RecordID", "Request", "RequestID = " & Me.Combo34)

    strSQL = "UPDATE Books_By_Library SET Available = True WHERE RecordID = " & nSQL

    CurrentDb.Execute strSQL1, dbFailOnError
    CurrentDb.Execute strSQL, dbFailOnError

    ----------------------------------

    UPDATE Available works fine, but updating the date string doesn't get executed. I get some message about Write Conflict and even when I choose Save Record it doesn't save it


    Thank you!

  13. #13
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,640
    The write conflict implies that you're trying to update the same table that the form is bound to. If so, you want to update via the form or code, but not both. If not, the error in the code is that date values need to be surrounded by # rather than ' (and no spaces between the delimiter and the value).
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  14. #14
    bogdan1245 is offline Novice
    Windows 7 32bit Access 2000
    Join Date
    Apr 2011
    Posts
    8
    Thank you so much for your help!


    I think I'm done for now.

    P.S. And I thought that taking CIS class would be easy...

  15. #15
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,640
    Happy to help!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Reference a text box in vba
    By forestgump in forum Forms
    Replies: 5
    Last Post: 04-25-2011, 08:55 AM
  2. need help with reference..
    By dada in forum Programming
    Replies: 3
    Last Post: 09-17-2010, 06:07 PM
  3. Reference table
    By Newcomer in forum Access
    Replies: 6
    Last Post: 06-03-2010, 03:08 PM
  4. Form Name Reference in VBA
    By Simon Sweet in forum Programming
    Replies: 0
    Last Post: 05-22-2008, 01:55 PM
  5. How to add Opensource reference???
    By loui in forum Access
    Replies: 0
    Last Post: 10-04-2007, 04:24 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums