VBA SQL Statement using IF Then ElseIF Ignore Zeros Populate List Box on a Form (1 Viewer)

gcoaster

Registered User.
Local time
Today, 04:12
Joined
Nov 5, 2007
Messages
32
Hello All!

I am trying to write VBA for a form using a SQL statement and need help, lots of help! what I would like to do is match values on a form (1 text box and 2 combo boxes)
with another table that match ID's
I would like to then populate an unbound List Box on the form with these values
excluding any values that are 0 (Zero)
something like this below ..

(I don't know how to word this I do apologize; I don't know if i should "if then else" or CASE or even how to format it for Access VBA. Not even sure where to insert WHERE. not sure about the "" and the & etc. etc. just need some simple examples please, Hope this does not cause some programmers to do a facepalm!)


Code:
dim mySQL as string
mySQL = "SELECT *  FROM tblPEOPLE Where tblPEOPLEID = [txtPERSONID]

IF
[PERSONID] =   ZERO THEN
Ignore, if its anything but zero add to mySQL to display in List Box
If its not [Forms]![frmMAIN].[cboPerson] = mySQL

IF
[PERSONID2] =  is ZERO THEN
mySQL =  [Forms]![frmMAIN].[cboPerson2]

IF
[PERSONID3] =  is ZERO THEN
mySQL =  [Forms]![frmMAIN].[cboPerson2]


EndIf
Me.listMAIN.RowSource = mySQL
    Me.listMAIN.Requery
End Sub
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 04:12
Joined
Aug 30, 2003
Messages
36,118
There's a lot wrong there. In the first line, you need to concatenate the form value. More info in this FAQ and the sample db:


Unless you've got a variable named ZERO, you have to test for the numeric value:

IF [PERSONID] = 0 THEN

you'd test for inequality with<>:

IF [PERSONID] <> 0 THEN

If you're not actually testing for the value of zero but whether the control has a value or not:

IF Len([PERSONID] & vbNullString) = 0 THEN

You're replacing the value in the variable when you do this:

mySQL = [Forms]![frmMAIN].[cboPerson2]

You'd want something like this to add to the end of it

mySQL = mySQL & [Forms]![frmMAIN].[cboPerson2]

You'll probably find this handy during testing:

 

gcoaster

Registered User.
Local time
Today, 04:12
Joined
Nov 5, 2007
Messages
32
Oh Sweet! thank you, I like your website. I have been there quite a few times but nothing like what I am requesting..
would it be something like this?

How would this be written in VBA to work in Access using VBA to populate the List Box Me.listRECORDS when the form Loads?

Code:
Dim mySQL As String
mySQL = "SELECT *  FROM tblPEOPLE "

Debug.Print mySQL
If Where [PERSONID1] <> 0 Then
mySQL = [Forms]![frmMAIN].[txtPerson1]

Debug.Print mySQL
If Where [PERSONID2] <> 0 Then
mySQL = [Forms]![frmMAIN].[cboPerson2]

Debug.Print mySQL
If Where [PERSONID3] <> 0 Then
mySQL = [Forms]![frmMAIN].[cboPerson3]


End If
Me.listMAIN.RowSource = mySQL
Me.listMAIN.Requery

End Sub
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 04:12
Joined
Aug 30, 2003
Messages
36,118
It doesn't seem like you read all my comments. Here's an example, though I do it a little differently now.

Code:
  Dim strSQL           As String
  Dim strWhere         As String
 
  On Error GoTo ErrorHandler

  strSQL = "Select ResNum, AcctID, PassName, ReqDateTime, CarType FROM tblReservations "
  'test for date entered
  If IsDate(Me.txtDate) Then
    strWhere = "WHERE ReqDateTime Between #" & Me.txtDate & " 00:00:00 am# And #" & Me.txtDate & " 11:59:59 pm#"
  End If
  'test for acct num entered
  If Len(Me.cboAcctNum & vbNullString) > 0 Then
    If Len(strWhere) > 0 Then
      strWhere = strWhere & " AND AcctID = " & Me.cboAcctNum
    Else
      strWhere = "WHERE AcctID = " & Me.cboAcctNum
    End If
  End If
  'test for pass name entered
  If Len(Me.txtPassName & vbNullString) > 0 Then
    If Len(strWhere) > 0 Then
      strWhere = strWhere & " AND PassName Like '%" & Me.txtPassName & "%'"
    Else
      strWhere = "WHERE PassName Like '%" & Me.txtPassName & "%'"
    End If
  End If
  'test for res number entered
  If Len(Me.txtResNum & vbNullString) > 0 Then
    If Len(strWhere) > 0 Then
      strWhere = strWhere & " AND ResNum = " & Me.txtResNum
    Else
      strWhere = "WHERE ResNum = " & Me.txtResNum
    End If
  End If

  If Len(strWhere) > 0 Then
    strSQL = strSQL & strWhere
    'Debug.Print strSQL
  Else
    MsgBox "No selections were made"
    Exit Sub
  End If

  Me.lstReservations.Visible = True
  Me.lstReservations.RowSource = strSQL
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 04:12
Joined
Aug 30, 2003
Messages
36,118
Also, it wouldn't make sense to do all the testing in the load event, since presumably the selections would be blank. If not, then you could have the code in the load event, or the current event if you also want it to fire when you changes records.
 

gcoaster

Registered User.
Local time
Today, 04:12
Joined
Nov 5, 2007
Messages
32
NICE!!!!! I am going to paste this into visual basic editor and see if i can get it to work.
 

gcoaster

Registered User.
Local time
Today, 04:12
Joined
Nov 5, 2007
Messages
32
I dont understand how the debug works you have to go to the live form and go to the next record to make debug work in vba editor?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 04:12
Joined
Aug 30, 2003
Messages
36,118
Well, you have to trigger the code somehow, whether it's clicking a button, loading the form, etc. In other words, trigger the event that the code is in.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 04:12
Joined
Aug 30, 2003
Messages
36,118
Oh, and you can't really paste my code in, as it has different controls, different SQL, etc. It was just to demonstrate how the code might look.
 

gcoaster

Registered User.
Local time
Today, 04:12
Joined
Nov 5, 2007
Messages
32
Oh, and you can't really paste my code in, as it has different controls, different SQL, etc. It was just to demonstrate how the code might look.

😀! I changed some lines and it works!
was getting an error but only because you are missing the line:
ErrorHandler

thank you!
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 04:12
Joined
Aug 30, 2003
Messages
36,118
Ah sorry, I failed to copy/paste the exit and error handlers. I can go get them if you need them.
 

Users who are viewing this thread

Top Bottom