Rabbit,
Basically, I just need to JOIN two unrelated tables (at a related field) so I can generate a query based on the results.
Forgetting the query piece for now - how can I use an INNER JOIN statement in VBA to join two tables?
If you look at my 3 lines of code, you can see how I was attempting to accomplish this.
Thanks again,
J
jverri01
Last Activity: May 29 '07, 01:14 PM
Joined: Apr 19 '07
Location:
-
Sorry, this is not clear to me. Can you give me an example that I can work from?...Leave a comment:
-
Problem with Join
Hi again, Rabbit.
I started the work on the next piece of this puzzle I'm developing.
I laid down most of the code, have one issue with a JOIN statement.
Can you take a look at this, tell me if you see where my problem is? It is definitley in this line of code:
...Code:strSQL = "SELECT * FROM [tbl_SponsorList] INNER JOIN [tbl_SponProd] ON [tbl_SponsorList].[Sponsor_ID] = [tbl_SponProd].[Sponsor_ID]
-
Rabbit,
I took your suggestion and added the single quotes. That corrected the problem. i tested it, worked fine, except it kept hanging up right at the end. I was about to ask your advice, but I noticed that I had not set echo back to Yes in the macro. I fixed that, and I now have a seemingly functional process.
Here is my final source code for this piece of the puzzle:
...Code:Private Sub Command16_Click()
Leave a comment:
-
OK. Thank you. I went in to the RESULTS table, and made sure there were comparable field names for those I am pulling from the other table. That helped.
Now, as I initiate the process, any time I choose an option OTHER THAN "-ALL-", the system prompts me with an Input Box, asks for a parameter value for whichever option I select from the drop-down.
So, this code works fine (if user selects "-ALL-"...Leave a comment:
-
Yet, the field does exist.
The table is named: tbl_SponProd
The field is named: Prod_Date
So, because both contain special characters, I have included them in brackets [ ]
J....Leave a comment:
-
Rabbit,
I have tried re-working the code several ways, all of which produce error messages.
For example, this source code gives me an error that the field does not exist:
...Code:If Me!Combo0 = " " Then MsgBox "Please make a valid selection from the list" Else strSQL = "INSERT INTO RESULTS SELECT [tbl_SponProd].[Prod_Date] FROM [tbl_SponProd] WHERE [Prod_Date] = "
Leave a comment:
-
Rabbit,
I have tried reworking this. I am still getting an error: Incomplete Query Clause
My re-worked code:
Code:If Me!Combo0 = " " Then MsgBox "Please make a valid selection from the list" Else strSQL = "INSERT INTO RESULTS SELECT * FROM [tbl_SponProd].{Prod_Date]" DoCmd.RunSQL strSQL End If
Am I missing somet...Leave a comment:
-
I'm sorry, Rabbit. I don;t understand this:
The only way to do so is to use a query or a seperate form. In which case you'll have to change query defs or the record source of a form
I am trying to build out an IF...THEN Block
If response in comboA = X Then Run some SQL Statement(s)
If response in comboB = Y Then Run some other SQL Statement(s)
Am I going about this wrong?
...Leave a comment:
-
OK. Where to begin?
As opposed to using DoCmd.RunSQl "SELECT..."
How about:
Will this work?...Code:strSQL = "SELECT * FROM [tbl_Div].[Div_Name]" DoCmd.RunSQL strSQL
Leave a comment:
-
Sorry - by the way - all the Macro does is open the RESULTS table......Leave a comment:
-
May just be better for me to "begin anew" ??...Leave a comment:
-
...Code:Private Sub Command16_Click() Dim strDate As Date Dim strDiv As String Dim strLoc As String Dim strVP As String Dim strSQL As String If IsNull(Combo0) Then strDate = " " Else strDate = [Forms]![manuallyadjustdata]![Combo0] End If If IsNull(Combo10) Then strDiv = " " Else strDiv = [Forms]![manuallyadjustdata]![Combo10]
Leave a comment:
-
Thanks.
I made a few modifications. I am still getting a bunch of error messages. My code is a big mess. I am not sure i am going about this in the most efficient manner. Is there a tutorial, or any threads you could point me to that may help me build this out better?
What I am doing:
I have a Form with 4 combo boxes (call them A, B, C, D) and a command button.
The top combo box (A) is a date value....Leave a comment:
-
Please help: Error with simple SQL statement
Hi. I am building a process that retrieves a value from a combo box, and based on the value, one of two SQL processes occur. If the value from the box is "-ALL-" then all records from a table are selcted for output. If a specific value is entered that is NOT "-ALL-", a separate process occurs (records from the table that match the value are stored as DIVISION).
This is the piece of code i have in place, and I kep... -
Hey, Rabbit!
I actually just figured this one out on my own (based on the information you provided me, originally).
Here is what I came up with, which is working fine:
Row Source is set to:
SELECT DISTINCT tbl_SponProd.Pr od_Date FROM tbl_SponProd;
Thanks!
J....Leave a comment:
-
Rabbit,
Are you able to give me an example, using this suggested statement...?
"SELECT DISTINCT ..." where ...
Again, I want only 1 occurrence (instance) of each value to appear in the drop-down
Thanks,
J....Leave a comment:
-
BTW - is there an easy way - say, directly within the control properties - to have only ONE instance of a recurring value appear in a drop-down list?...Leave a comment:
-
Hurray! We did it. And, I still have 5 minutes before I meet with my boss! What can we work on now, LOL!
Thanks, Rabbit, for your help.
I'm sure I'll be back in an hour, when I have my next project...
J...Leave a comment:
No activity results to display
Leave a comment: