By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,149 Members | 1,788 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,149 IT Pros & Developers. It's quick & easy.

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

gcoaster
100+
P: 117
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:

Expand|Select|Wrap|Line Numbers
  1. Dim MyLameSQL As String
  2. MyLameSQL = "SELECT * FROM [tblRECORDS] From tblRECORDS
  3.  
  4. IF tblRECORDS.recordid is not zero and matches forms!frmMAIN!cboPerson Then
  5. Add it to MyLameSQL 
  6. IF tblRECORDS.Recordid2 is not zero and matches forms!frmMAIN!cboPerson2 Then
  7. Add it to MyLameSQL 
  8. IF tblRECORDS.Recordid3 is not zero and matches forms!frmMAIN!cboPerson3 Then 
  9. Add it to MyLameSQL 
  10.  
  11. Me.listRECORDS.RowSource = MyLameSQL 
  12. Me.listRECORDS.Requery
  13.  
Thanks in advanced!
4 Weeks Ago #1

✓ answered by twinnyfo

gcoaster,

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

Expand|Select|Wrap|Line Numbers
  1. Dim MyLameSQL As String
  2.  
  3. MyLameSQL = _
  4.     "SELECT * FROM tblRECORDS " & _
  5.     "WHERE " & _
  6.         "(tblRECORDS.Recordid <> 0 " & _
  7.             "AND tblRECORDS.Recordid = " & Me.cboPerson & ") "
  8.         "AND (tblRECORDS.Recordid2 <> 0 " & _
  9.             "AND tblRECORDS.Recordid2 = " & Me.cboPerson2 & ") "
  10.         "AND (tblRECORDS.Recordid3 <> 0 " & _
  11.             "AND tblRECORDS.Recordid3 = " & Me.cboPerson3 & ");"
  12.  
  13. Me.listRECORDS.RowSource = MyLameSQL 
  14. 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!

Share this Question
Share on Google+
7 Replies


NeoPa
Expert Mod 15k+
P: 31,761
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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM   [tblMain]
  3.        INNER JOIN
  4.        [tblRecords]
  5.   ON   [tblMain].[ID]=[tblRecords].[ID]
  6. WHERE  ([tblMain].[ID]={whatever})
4 Weeks Ago #2

twinnyfo
Expert Mod 2.5K+
P: 3,482
gcoaster,

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

Expand|Select|Wrap|Line Numbers
  1. Dim MyLameSQL As String
  2.  
  3. MyLameSQL = _
  4.     "SELECT * FROM tblRECORDS " & _
  5.     "WHERE " & _
  6.         "(tblRECORDS.Recordid <> 0 " & _
  7.             "AND tblRECORDS.Recordid = " & Me.cboPerson & ") "
  8.         "AND (tblRECORDS.Recordid2 <> 0 " & _
  9.             "AND tblRECORDS.Recordid2 = " & Me.cboPerson2 & ") "
  10.         "AND (tblRECORDS.Recordid3 <> 0 " & _
  11.             "AND tblRECORDS.Recordid3 = " & Me.cboPerson3 & ");"
  12.  
  13. Me.listRECORDS.RowSource = MyLameSQL 
  14. 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!
4 Weeks Ago #3

gcoaster
100+
P: 117
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.
3 Weeks Ago #4

gcoaster
100+
P: 117
Thank NeoPa! I can also work with this for my project.
3 Weeks Ago #5

gcoaster
100+
P: 117
Here is the code that ended up working



Expand|Select|Wrap|Line Numbers
  1. '\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
  2. '\\\   a SQL Statement in VBA ACCESS using OR AND togeather
  3. '\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
  4. '***********************************************************************
  5. 'RecMainID = txtMainID
  6. 'RecChildID = cboCHILD
  7. 'RecSpouseID = cboSpouse1
  8. '**********************************************************************
  9. Private Sub Form_Current()
  10. Dim strRecords As String
  11. strRecords = "SELECT tblRECORDS.AttachmentDate, tblRECORDS.AttachmentType, tblRECORDS.AttachmentTitle, * FROM tblRECORDS WHERE " & _
  12.     "([RecMainID] = txtMainID OR [RecChildID] = txtMainID OR txtMainID = RecSpouseID) " & _
  13.     "OR ([RecChildID] <> 0 AND [RecChildID] = FORMS!frmFAMILY!cboCHILD) " & _
  14.     "OR ([RecSpouseID] <> 0 AND [RecSpouseID] = FORMS!frmFAMILY!cboSpouse1) ORDER BY tblRECORDS.AttachmentDate;"
  15. Me.listRECORDS.RowSource = strRecords
  16. Me.listRECORDS.Requery
  17. End Sub
  18.  
3 Weeks Ago #6

NeoPa
Expert Mod 15k+
P: 31,761
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.
3 Weeks Ago #7

gcoaster
100+
P: 117
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
3 Weeks Ago #8

Post your reply

Sign in to post your reply or Sign up for a free account.