• We’re currently investigating an issue related to the forum theme and styling that is impacting page layout and visual formatting. The problem has been identified, and we are actively working on a resolution. There is no impact to user data or functionality, this is strictly a front-end display issue. We’ll post an update once the fix has been deployed. Thanks for your patience while we get this sorted.

Access VBA programming

Z

zerocool1

Diamond Member
What I'm trying to accomplish-
I'm trying to use access to append info such as form #, state, transaction type and a few other variables, into a temporary table. This table will then be exported to Excel.

Questions
  • how do you increment a recordset running a query with 2 joins?
  • is there a difference in the application in dao and ado, like ado should be used for x and dao should be used for y?



Solved
  • how do you do queries in line? I'm trying to do a fairly large query but so i can't store it into a string any ideas?
  • what is a type mismatch error

Amit

Dim db As Database
Dim query As QueryDef
Dim stDocName As String
Dim stTranstype As String
Dim stState As String
Dim qry As String
Set db = CurrentDb
Dim tmpbl As Recordset
Dim T1 As Recordset
Dim Transtype As Recordset
Dim SSV As Recordset
Dim insert As Recordset
SSV.OpenRecordset = db.OpenRecordset("state specific variations", dbOpenSnapshot) Transtype.OpenRecordset = db.OpenRecordset("Trans type", dbOpenSnapshot) T1.OpenRecordset = db.OpenRecordset("Table1", dbOpenSnapshot)
tmpbl.OpenRecordset = db.OpenRecordset("temptbl", dbOpenSnapshot)
stState = cmbState.Text
stTranstype = cmbTrans.Text
Do While Not T1.EOF
qry = "INSERT INTO tmpbl ( [Insert No], [Insert Replaced], [description of communication], [type of transaction], State )" qry = qry & " SELECT T1![Insert No], Replacement![Insert Replaced], T1![description of communication], Transtype!Abbrev, T1!State"
qry = qry & " FROM ((Transtype INNER JOIN T1 ON Transtype!ID = T1![type of transaction]) LEFT JOIN Replacement ON T1![Insert No] = Replacement![Insert Number]) LEFT JOIN SSV ON T1![Insert No] = SSV![state specific form]"
qry = qry & " WHERE (((Transtype!Abbrev) Like "*" & [trans type] & "*") AND ((Table1.State) Like "--" Or (T1.State) Like "*" & [staten] & "*"));
Loop
DoCmd.RunSQL qry

SSV.Close
Transtype.Close
T1.Close
tmpbl.Close
 
Some snippets from code I use

Const adOpenStatic = 3
Const adLockOptimistic = 3
const adUseClient = 3

Set objConnection = createObject("ADODB.Connection")
Set objRecordset = createObject("ADODB.Recordset")
objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbFile
objRecordset.CursorLocation = adUseClient
objRecordset.Open "SELECT * FROM Computers" , objConnection, adOpenStatic, adLockOptimistic

strSearchCriteria = "Workstation = '" & strComputerName & "'"

' Making the computer name Lowercase so that it is always unique
computerName=LCase(strComputerName)
objRecordSet.Find strSearchCriteria

Jim
 
<div class="FTQUOTE"><begin quote>Originally posted by: yinan
Some snippets from code I use

Const adOpenStatic = 3
Const adLockOptimistic = 3
const adUseClient = 3

Set objConnection = createObject("ADODB.Connection")
Set objRecordset = createObject("ADODB.Recordset")
objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbFile
objRecordset.CursorLocation = adUseClient
objRecordset.Open "SELECT * FROM Computers" , objConnection, adOpenStatic, adLockOptimistic

strSearchCriteria = "Workstation = '" & strComputerName & "'"

' Making the computer name Lowercase so that it is always unique
computerName=LCase(strComputerName)
objRecordSet.Find strSearchCriteria

Jim</end quote></div>


do I need to be using ADO?
EDIT: do I need to put the select section of the query in quotes? what if i'm doing an append query?
 
A recordset is just that, a set of records. Typically either a table or the data returned for a query.
A type mismatch error indicates that you have attempted to use a variable of one type in a place where another is supposed to be used, and there isn't an implicit cast available. Trying to put a boolean into a date, or a string into an integer.

 
Need more details. Does the user key in this data? Are you trying to query the data from another source?
 
Originally posted by: PhatoseAlpha
A type mismatch error indicates that you have attempted to use a variable of one type in a place where another is supposed to be used, and there isn't an implicit cast available. Trying to put a boolean into a date, or a string into an integer.
Click to expand...

that's what i figured.
 
Originally posted by: KLin
Need more details. Does the user key in this data? Are you trying to query the data from another source?
Click to expand...

the user is selecting data from 2 combo boxes and a few option boxes and the query appends records to another table based upon what is inputted.
 
Originally posted by: zerocool1
What I'm trying to accomplish-
I'm trying to use access to append info such as form #, state, transaction type and a few other variables, into a temporary table. This table will then be exported to Excel.

Questions
  • how do you increment a recordset running a query with 2 joins?
  • is there a difference in the application in dao and ado, like ado should be used for x and dao should be used for y?



Solved
  • how do you do queries in line? I'm trying to do a fairly large query but so i can't store it into a string any ideas?
  • what is a type mismatch error

Amit

Dim db As Database
Dim query As QueryDef
Dim stDocName As String
Dim stTranstype As String
Dim stState As String
Dim qry As String
Set db = CurrentDb
Dim tmpbl As Recordset
Dim T1 As Recordset
Dim Transtype As Recordset
Dim SSV As Recordset
Dim insert As Recordset
SSV.OpenRecordset = db.OpenRecordset("state specific variations", dbOpenSnapshot) Transtype.OpenRecordset = db.OpenRecordset("Trans type", dbOpenSnapshot) T1.OpenRecordset = db.OpenRecordset("Table1", dbOpenSnapshot)
tmpbl.OpenRecordset = db.OpenRecordset("temptbl", dbOpenSnapshot)
stState = cmbState.Text
stTranstype = cmbTrans.Text
Do While Not T1.EOF
qry = "INSERT INTO tmpbl ( [Insert No], [Insert Replaced], [description of communication], [type of transaction], State )" qry = qry & " SELECT T1![Insert No], Replacement![Insert Replaced], T1![description of communication], Transtype!Abbrev, T1!State"
qry = qry & " FROM ((Transtype INNER JOIN T1 ON Transtype!ID = T1![type of transaction]) LEFT JOIN Replacement ON T1![Insert No] = Replacement![Insert Number]) LEFT JOIN SSV ON T1![Insert No] = SSV![state specific form]"
qry = qry & " WHERE (((Transtype!Abbrev) Like "*" & [trans type] & "*") AND ((Table1.State) Like "--" Or (T1.State) Like "*" & [staten] & "*"));
Loop
DoCmd.RunSQL qry

SSV.Close
Transtype.Close
T1.Close
tmpbl.Close
Click to expand...

You're not looping through the records of the T1 recordset. you need to add T1.MoveNext after you execute docmd.runsql qry

The type mismatch error might be coming in the qry string. The joined fields are possible not the same datatype. You can step through code.

While in the VBA editor window, if you click in the gray narrow area next to the lines of code, it will create a brown dot(a breakpoint). Then when the code is ran, it will stop at that breakpoint. Hit F8 to step through lines of code one at a time, or F5 to continue on with the code execution.
 
You must log in or register to reply here.

TRENDING THREADS

Back
Top