How to write SQL statements in codes?

[latex88]

I have never used SQL in codes before. I don’t know the syntax or the structure of writing a SQL statement in codes. Every time I need to check a value, I would write a query to pass the value of a field to a text box of a form, then in the code, I would reference that text box. It is not efficient and I always need to have that form open.


If this has been discussed, I would appreciate if someone can reference the thread. Thanks for the help.

 

[dcx693]

Your explanation is a bit confusing. Can you give a more concrete example?

 

[Fornatian]

In many instances where people write an SQL Statement, there is no need to, instead use a presaved update query and run that from your code:

Docmd.OpenQuery "QueryName"

The next question usually posted is how do I stop it notifying me that it has updated a record, this is done by surrounding the OpenQuery with:

Docmd.SetWarnings False
Docmd.OpenQuery "QueryName"
Docmd.SetWarnings True

It is important to setwarnings back to true or you may miss other warnings which you do want to see.

If you want to update a value on a current form you may do so by directly referencing the control:

me.controlname = value

still not sure what you want...

 

[evilman]

hmmmm.... so you're saying that in your query you have a "where" condition like this:
SomeField = Forms!MyForm!txtSomeTextField ?

You could use a global vba function to return a value, but that would imply that you'd have to run some code prior to the query execution.
Then you'd have to set your "where" condition to something like this: SomeField = SomeGlobalVBAFunction()

If you need the data in a report, or in a form, you can remove all "where" conditions and use the form's or report's Filter property (that wont work if you're using a "having" condition, though).

 

[latex88]

Perhaps I was not very clear in my request for help. Basically, I have a form that requires the user to enter basic information in the MAIN FORM prior to making an order. The detailed order is made in a different form, DETAIL FORM. I have a button that allows the user to conclude the ordering process in the MAIN FORM, as well as, the DETAIL FORM. The problem comes when no quantity is selected or certain fields are left blank, and the button to conclude is pushed, the report cannot find certain values and I get an Error message and the codes stop running. Therefore, I would like to check for certain fields, such as quantity > 0 before I allow the button to be “enabled”. I hope this is much clearer.

 

[evilman]

Create a general procedure that will perform checks on every control, and enable it if they meet your criterias, or disable it if they don't
Then call this general procedure at the LostFocus event of every control that need to be checked.

Public Sub CheckCriterias()
Dim ButtonEnabled as Boolean

ButtonEnabled = True
if (CLng(txtQuantity) < 0 And Not IsNumeric(txtQuantity)) Then
ButtonEnabled = False
End If

if (IsNull(txtClient) Then
ButtonEnabled = False
End If

cmdButton.Enabled = ButtonEnabled
End Sub

 

[latex88]

Evil,

I follow your logic. However, two questions...

1) what if the field, txtQuantity, has several records, meaning there are several records that have the field txtQuantity since the form is getting its data from a query? see below. I would imagine I would have to add another field to sum the quantity and then check the sum?

Product qty
xxxx 0
yyyy 0
zzzz 0

2) is there a way to check for txtQuantity if the form containing the above data is not opened?

 

[evilman]

1) I would use a recordset to execute a simple query and gather the data..
Something like this:

Dim rs as New Adodb.Recordset

rs.open "select Sum(Qty) AS SumQty from TblProducts where ProductName = '" & txtProductName & "' GROUP BY ProductName;", CurrentProject.Connection, adOpenForwardOnly

if (CLng(txtQuantity) > rs!SumQty) Then
ButtonEnabled = False
End If
rs.close

opening a recordset might slow down your form's response time, so you could put the code on txtQuantity and pass the result as a parameter to "CheckCriterias".

2) no you can't, but if you do it, access will open the form anyway ... so you'll probably get a Null value.

 

[Travis]

Just a note: evilman gave you an example of using ADO to open a recordset.

I recommend using the QBE Grid to write and Debug you SQL statements prior to using them in code. Look up RecordSets to see different methods on how to create them. Also look into to DLookup for how to return single fields from a table.

 

[latex88]

The recordset method seems quite complicated. I looked into the Dlookup deal and I think it may provide what I need. However, as a beginner of code writing, I was not able to come up with it after a long period of coding. Maybe I'm naive, but can't I just write a SQL statement to sum all the quantity and check that result to see if greater than 0?

If my assumption is true, how do I write that SQL statement?

 

[Fornatian]

The problem comes when no quantity is selected or certain fields are left blank, and the button to conclude is pushed, the report cannot find certain values and I get an Error message and the codes stop running. Therefore, I would like to check for certain fields, such as quantity > 0 before I allow the button to be “enabled”. I hope this is much clearer.

It seems to me you need data validation using the before update event to cancel the record being saved if the user does not complete fields. There are many posts on this forum explaining how to do this.