Populate a List Box on a Form using VBA SQL Statement If Then ElseIf

[gcoaster]

Hello All

First of all, I am totally hopeless in writing SQL correctly in VBA.

I will try and be as descriptive as I can possible be and attempt to use the correct terminology and correct VBA functions! ;-)

What I am attempting is populating a unbound list box (listRECORDS) on a form that populates using SQL on form Load

The form is frmMAIN and the source is tblMAIN
There is another table and the table is tblRECORDS
I am not sure if I should use If Then Else OR Case
I would also like to keep it as simple as I can and add to it as I learn.

Ok, I would like to see how this is formated and done in VBA Access:

Dim MyLameSQL As String
MyLameSQL = "SELECT * FROM [tblRECORDS] From tblRECORDS

IF tblRECORDS.recordid is not zero and matches forms!frmMAIN!cboPerson Then
Add it to MyLameSQL 
IF tblRECORDS.Recordid2 is not zero and matches forms!frmMAIN!cboPerson2 Then
Add it to MyLameSQL 
IF tblRECORDS.Recordid3 is not zero and matches forms!frmMAIN!cboPerson3 Then 
Add it to MyLameSQL 

Me.listRECORDS.RowSource = MyLameSQL 
Me.listRECORDS.Requery

Thanks in advanced!

[NeoPa]

Hi GCoaster.

I accept and respect the attempt to explain yourself clearly. I'm afraid, in this case, I'm relatively clueless as to your meaning nevertheless. Let's see if I can draw something out of it regardless.

Would I be right to assume you have two tables where one has a single item that matches your selected ID and the other has zero to many items that all (or none) could match that same ID?

If that is the situation you're trying to describe in unfamiliar languae & notions, then we may be in luck. This sort of thing is a mainstay in database work and is easily handled by SQL. In this case it would go something along the lines of :

SELECT *
FROM   [tblMain]
       INNER JOIN
       [tblRecords]
  ON   [tblMain].[ID]=[tblRecords].[ID]
WHERE  ([tblMain].[ID]={whatever})

[twinnyfo]

gcoaster,

I'm not sure if I've completely grasped what you are after, but here is my attempt at clarifying:

Dim MyLameSQL As String

MyLameSQL = _
    "SELECT * FROM tblRECORDS " & _
    "WHERE " & _
        "(tblRECORDS.Recordid <> 0 " & _
            "AND tblRECORDS.Recordid = " & Me.cboPerson & ") "
        "AND (tblRECORDS.Recordid2 <> 0 " & _
            "AND tblRECORDS.Recordid2 = " & Me.cboPerson2 & ") "
        "AND (tblRECORDS.Recordid3 <> 0 " & _
            "AND tblRECORDS.Recordid3 = " & Me.cboPerson3 & ");"

Me.listRECORDS.RowSource = MyLameSQL 
Me.listRECORDS.Requery

Your code is a bit confusing, as it may imply that you only want to test the values of Person1, 2 and 3 if they are not 0, but I'm not sure if you want to 1) exclude records from the query if the person in tblRecords is 0 or 2) you only want to search for the person if the combo box is not 0 (which, honestly, is a more standard approach, since the Person in tblRecords would very seldom be 0--although it could be).

Hope this hepps!

[gcoaster]

Excellent! thank you twinnyfo
Exactly what I was looking for! I just have to replace AND with OR

I would also like to add
If Then Else ElseIf but i can figure this out.

[gcoaster]

Thank NeoPa! I can also work with this for my project.

[gcoaster]

Here is the code that ended up working

'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
'\\\   a SQL Statement in VBA ACCESS using OR AND togeather
'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
'***********************************************************************
'RecMainID = txtMainID
'RecChildID = cboCHILD
'RecSpouseID = cboSpouse1
'**********************************************************************
Private Sub Form_Current()
Dim strRecords As String
strRecords = "SELECT tblRECORDS.AttachmentDate, tblRECORDS.AttachmentType, tblRECORDS.AttachmentTitle, * FROM tblRECORDS WHERE " & _
    "([RecMainID] = txtMainID OR [RecChildID] = txtMainID OR txtMainID = RecSpouseID) " & _
    "OR ([RecChildID] <> 0 AND [RecChildID] = FORMS!frmFAMILY!cboCHILD) " & _
    "OR ([RecSpouseID] <> 0 AND [RecSpouseID] = FORMS!frmFAMILY!cboSpouse1) ORDER BY tblRECORDS.AttachmentDate;"
Me.listRECORDS.RowSource = strRecords
Me.listRECORDS.Requery
End Sub

[NeoPa]

Hi GCoaster.

Are you sure you've copied & pasted your code across reliably? Line #14 could never compile even. It has what are probably the last two characters (;") repeated. I'm sure it's a simple copy/paste error but you should check it's what you intended to post.

[gcoaster]

Your right, I corrected it [edit]

AttachmentDate; "

At least I took the time to post the solution for those in the future, that need help! Hope this helps someone