help for newbie , DoCmd.RunSql

[scoob8254]

hello,

im creating a small database and am extremely new to vba, ive got stuck on running a select query from within the vba code itself, the book im currently reading while learning about this stuff suggests my code whould work but after looking into it i believe its wrong as ive read various threads saying you cannot use docmd.runsql with a select query, just wondering if anyone could help and throw some light on how to get this little bit of code working. below is the part of my code that falls over,

basicaly it should lookup the weight based on what the parcel type is and find the price, the parcel type is worked out earlier in my code and is held in strParcelType, theirs probably an easier way to do this as well but have'nt got that far in my book

intWeight = Me.txtWeight

Select Case intWeight
Case 0 To 100
intCost = DoCmd.RunSQL "SELECT pricetable1.[Postage Name], pricetable1.[0-100g] FROM pricetable1 WHERE (((pricetable1.[Postage Name]) = strParcelType))"
Case 101 To 250
intCost = DoCmd.RunSQL "SELECT pricetable1.[Postage Name], pricetable1.[101-250g] FROM pricetable1 WHERE (((pricetable1.[Postage Name]) = strParcelType))"
Case 251 To 500
intCost = DoCmd.RunSQL "SELECT pricetable1.[Postage Name], pricetable1.[251-500g] FROM pricetable1 WHERE (((pricetable1.[Postage Name]) = strParcelType))"
Case 501 To 750
intCost = DoCmd.RunSQL "SELECT pricetable1.[Postage Name], pricetable1.[501-750g] FROM pricetable1 WHERE (((pricetable1.[Postage Name]) = strParcelType))"
Case 751 To 1000
intCost = DoCmd.RunSQL "SELECT pricetable1.[Postage Name], pricetable1.[751-1000g] FROM pricetable1 WHERE (((pricetable1.[Postage Name]) = strParcelType))"
Case 1001 To 1250
intCost = DoCmd.RunSQL "SELECT pricetable1.[Postage Name], pricetable1.[1001-1250g] FROM pricetable1 WHERE (((pricetable1.[Postage Name]) = strParcelType))"
End Select

Me.txtEstimate = strParcelType & intCost

many thanks to any gurus who can point me in the right direction or show me a easier way to do this

 

[pbaldy]

Welcome to the site. As you've heard, you can't use RunSQL with a SELECT query. Your 2 basic options are using DLookup or opening a recordset. The recordset is more efficient, particularly since you want 2 values. Here are the basics:

  Dim strSQL  As String
  Dim db      As DAO.Database
  Dim rs      As DAO.Recordset

  Set db = CurrentDb()
  
  strSQL = "SELECT ..."
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

  VariableName = rs!FieldName

  set rs = nothing
  set db = nothing

 

[scoob8254]

thanks very much

 

[scoob8254]

hi again, woke up today and thought would look at your suggestion but i must be missing something or i have completly got the wrong idea as i get an error

Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset

intWeight = txtWeight
Set db = CurrentDb()
strSQL = "SELECT pricetable1.[Postage Name] FROM pricetable1 WHERE (((pricetable1.[Postage Name])=strParcelType))"
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

Select Case intWeight
Case 0 To 100
intCost = rs![0-100g]
Case 101 To 250
intCost = rs![101-250g]
Case 251 To 500
intCost = rs![251-500]
Case 501 To 750
intCost = rs![501-750g]
Case 751 To 1000
intCost = rs![751-1000g]
Case 1001 To 1250
intCost = rs![1001-1250g]
End Select

txtEstimate = strParcelType & " " & intCost

Set rs = Nothing
Set db = Nothing

many thanks in advance to you guys

 

[pbaldy]

Presuming the value is text as its name implies:

strSQL = "SELECT pricetable1.[Postage Name] FROM pricetable1 WHERE pricetable1.[Postage Name]= '" & strParcelType & "'"

 

[scoob8254]

many thanks, will try it when get home from work, appreciate your help

 

[pbaldy]

No problem. If you still get an error, it would help to know what it is and what line is highlighted. You might make sure you have the MS DAO reference selected in Tools/References (VBA editor).

 

[Vincen85]

From SQL view statement and plugging it DoCmd.RunSQL

Hi, My name is Vincent

I'm very new to Microsoft access and what I want to do with microsoft access is to combine two table1 & table2. So what I did was that I created a query to combine the two tables and extract the datas from the two table I that I needed.

For Example:
Table1 has data1,data2,and data3
and in Table2 it has data1,data2, data3, data 4 and data 5

What I'm trying to do is pretty much combine the data in Table1 & Table2 in a Query to include only: Data1,Data2,and Data3 from table1 and Data4, and Data5 from table2.

So What I did was create a Query to combine them together and assign their relationship to match the order of the data. However, Now I want to create somekind of shortcut to do this compiling....which is why I'm trying to use SQLview data given from the Query to create a RunSQL that automatically do the above.

The SQLview i got is below:
SELECT SessionData.RunNumber, SessionData.SessionID, SessionData.SequenceNo, SessionData.SubjectID, SessionData.Latency, SessionData.BeamBrk1, SessionData.BeamBrk2, SessionData.BeamBrk3, SessionHeader.SessionDate, SessionHeader.ChamberNo, SessionHeader.GroupID, SessionHeader.SubjectVariables, SessionHeader.TestDefName
FROM SessionData INNER JOIN SessionHeader ON (SessionData.SubjectID = SessionHeader.SubjectID) AND (SessionData.RunNumber = SessionHeader.RunNumber);

Im really confuse on how to put this into the runSQL. What I know is that some guy on a different websites stated that I can only use ADODB to do a SELECT on SQL because runSQL does not support SELECT......

I'm a really new on VB too and I real need of a professional help!

If I dont explain it clearly please let me know and I'll try my best to clear things out.

Thank you for reading this!

Ps. can you give me a complete coding if you are planning to state one. I really appreciate what you are doing!

 

[pbaldy]

Well, it's true that you can't use runSQL with a SELECT query, only action queries (append, update, etc). What is it you're trying to accomplish? Typically you'd just save that query and open it when you need that data (more commonly base a form or report on it).

 

[Vincen85]

pretty much what I am trying to accomplish here is to automatically create a query using:

The SQLview i got is below:
SELECT SessionData.RunNumber, SessionData.SessionID, SessionData.SequenceNo, SessionData.SubjectID, SessionData.Latency, SessionData.BeamBrk1, SessionData.BeamBrk2, SessionData.BeamBrk3, SessionHeader.SessionDate, SessionHeader.ChamberNo, SessionHeader.GroupID, SessionHeader.SubjectVariables, SessionHeader.TestDefName
FROM SessionData INNER JOIN SessionHeader ON (SessionData.SubjectID = SessionHeader.SubjectID) AND (SessionData.RunNumber = SessionHeader.RunNumber);

and eventually make this code that I program to be a shortcut to run the above Query of the Table1 & Table2.

So that In future I do not have to assign the data from each tables to make the query and instead just click on the code that I hopefully can be made to create the above query.

I'm trying to do this because everytime I want to add a data into a new table I created that include data1, data2 and data3 from table1 + data4, data5 from table2. I have to set their relationship too. So, In trying to do this task hassle free....I'm trying to make a VB command that can do this....


I hope this is clear enough....

 

[pbaldy]

I'm not sure I'm clear about the goal here. There's nothing dynamic about that query that would require it to be built in code. You could simply save that query and open it with:

DoCmd.OpenQuery "QueryName"

 

[Vincen85]

My ultimate goal here is to create a VB program that does what I did to get set up the Query for the table1 and Table2. Almost like macro that can do this routine without me clicking through Query Wizard or Setup to create a Query for Table1 and Table2 in my example. (I tried using macro but it is very limited what I can do with it)


(The Image I presented below, is the Query that I made using the Simple Query Wizard....and the goal here is for me to make the VB program that does everything to set up the query, and relationship between the table data, just like the one I made with Simple Query Wizard)


I apologize for my unclear and messy explaination.