CODEWITHSUNDEEP
vbams-access
Daniel
Daniel

Reputation: 73

MS Access VBA IF()

I am trying to write an if loop for MS Access VBA

In php I would write:

if($query=="0"){
  run query 1
} else {
  run query 2
}

I do need to call the query in and refer to multiple tables (ie. 'query 1' will extract data from a combo box, whereas, 'query 2' will run another query)

------------------------------------EDIT: 02/06/14 13:34 AEST ----------------------------

Dim variabl1 As String
variabl1 = Me.cmbItemDetails.Column(1)
Dim variabl2 As String
variabl2 = "" & Forms!frmRaiseOrder!cmbDebtorCode & ""
'you can use variable as a parameter in SQL, but only if you hardcode your SQL     statement as a string... See directly below

Dim SQL As String
SQL_count = "SELECT COUNT(CustItemPrice) FROM tblSpecialPricing WHERE ItemListID = '" &     variabl1 & "' AND CustListID = '" & variabl2 & "' "
SQL_select = "SELECT CustItemPrice FROM tblSpecialPricing WHERE ItemListID = '" &     variabl1 & "' AND CustListID = '" & variabl2 & "' "


Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset(SQL_count)
If rs.RecordCount = "0" Then
   Me.txtStreetPrice.Value = Me.cmbItemDetails.Column(3)
Else
   DoCmd.OpenQuery "qrySelectCustomerName"
End If

Thanks to @MurDeR for the assistance for the above statements






----------------------------------------------- Update as of 9th July -----------------------------------

Hi everyone;
If you are trying to create an IF() statement in ACCESS VBA, use the following syntax...

Private Sub cmbItemDetails_Change()

Me.txtItemDescription.Value = Me.cmbItemDetails.Column(2)
Me.txtStreetPrice.Value = Me.cmbItemDetails.Column(3)
Me.txtItemName.Value = Me.cmbItemDetails.Column(1)

Dim variabl1 As String
variabl1 = Me.cmbItemDetails.Column(0)
Dim variabl2 As String
variabl2 = "" & Forms!frmRaiseOrder!cmbDebtorCode & ""
'you can use variable as a parameter in SQL, but only if you hardcode your SQL statement as a string... See directly below

Dim SQL As String
SQL_count = "SELECT COUNT(CustItemPrice) FROM tblSpecialPricing WHERE ItemListID = '" & variabl1 & "' AND CustListID = '" & variabl2 & "' "
SQL_select = "SELECT CustItemPrice FROM tblSpecialPricing WHERE ItemListID = '" & variabl1 & "' AND CustListID = '" &     variabl2 & "' "

Dim rs As Recordset
Set rs = Nothing
Set rs = CurrentDb.OpenRecordset(SQL_count)
RecordCount = rs.Fields(0)

If RecordCount = "1" Then
   'SPECIAL PRICE EXISTS - this code will run only if the count query is greater than zero
   MsgBox "Special Price Exists", vbOkay, "Alert"
   '
   '
   Me.txtUnitPrice.Value = Me.cmbItemDetails.Column(3)
   'Me.txtUnitPrice.Value = "" & Forms!frmRaiseOrder!subformCreateOrder!frmSelectCustomPriceinsubform!CustItemPrice & ""
   'Me.txtUnitPrice.Value = Me.subfrmItemPrice.CustItemPrice.Value
Else
   'NO SPECIAL PRICE - this code will run only if the coutn query is zero
   'MsgBox "No Special Pricing for this item", vbOkay, "Alert"
   '
   '
   Me.txtUnitPrice.Value = Me.cmbItemDetails.Column(3)
End If

End Sub




I would really like to thank and credit @MurDeR for their help and also if you have any questions, PM me or post here

Upvotes: 2

Views: 2827

Answers (1)

Mark C.
Mark C.

Reputation: 6450

You can work with a recordset in VBA.

See this bit of code for a start.

Dim variabl As String
variabl = Me.ComboBox1.Value
'you can use variabl as a parameter in SQL, but only if you hardcode your SQL statement as a string... See directly below

Dim SQL As String
SQL = "SELECT * FROM TableA WHERE YourColumnName = '" & variabl & "'"


Dim db As DAO.Database
db = CurrentDb
Dim rs As DAO.Recordset
Set rs = db.OpenRecordset("SQL Statement or Query Name")
If rs.RecordCount = 0 Then
DoCmd.OpenQuery "Query1"
Else
DoCmd.OpenQuery "Query2"
End If
rs.Close
Set rs = Nothing

Let me know if you have any other questions. Also note, you need to make sure you're entering proper query names. I know VBA has pathetic intellisense, so this can be tricky when entering query names as strings.

Upvotes: 2

Related Questions