How to Reference a text box in VBA

[bogdan1245]

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!

[pbaldy]

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"

[bogdan1245]

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!

[pbaldy]

How are you executing the SQL? Do you get an error? What is the data type of the RecordID field?

[bogdan1245]

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

[pbaldy]

That just populates a string variable; you have to execute it. Add this line after that one:

CurrentDb.Execute strSQL, dbFailOnError

[bogdan1245]

It worked! Thank you so much! At least now I have an idea how VBA works. I just never programmed on it before.

[bogdan1245]

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!

[pbaldy]

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.

[bogdan1245]

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!

[pbaldy]

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

[bogdan1245]

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!

[pbaldy]

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).

[bogdan1245]

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...

[pbaldy]

Happy to help!