Hi,
I am trying to run a query in an access DB from word VBA code.
This is what I am trying to do:
1. From a userform extract values from two drop down lists.
2. Use these values to find a specific record in a database
3. From that record get a value from a specific field and use it in the VBA code again.
I am confused about how to do this and have found CreateQueryDef, but am not sure it is correct to use it.
Confusions:
1. How do I search properly in the database (SQL, seek, find etc statements)
2. How do I set the pointer to THE found record?
3. How do I present the value back in word VBA?
4. If you have any good tips, hints and code I am most greatful!
Have a sunny day!
/Per
This is the code I have been "fiddling with" but it does not work for sure ;-)! (PS! I am Swedish so there might be some strange words here and there)
Sub test_of_query()
'Typ av CV som skall inkluderas
Dim dbJob As database
Dim rstTemp As Recordset
Dim strSQL As String
Set dbJob = OpenDatabase Name:=ActiveDocument.AttachedTemplate.Path + "\job.mdb"
' Open recordsets from the Definitioner table
Set rstDefinitions = dbJob.OpenRecordset("Definitioner", dbOpenDynaset)
'Set qdfDefinitioner = dbJob.CreateQueryDef(
'"TestOfQueryDef", "SELECT * FROM Defintioner WHERE Language =" + ComboBox1.Value + " AND Competency = " + ComboBox2.Value) 'This code is delimited and is the final code I will try to use, but I am running a trial below
strSQL = "SELECT * FROM Defintioner WHERE Language = English AND Competency = Project Management"
Set rstTemp = dbJob.OpenRecordset(strSQL)
Debug.Print rstTemp
dbJob.Close
End Sub
I am trying to run a query in an access DB from word VBA code.
This is what I am trying to do:
1. From a userform extract values from two drop down lists.
2. Use these values to find a specific record in a database
3. From that record get a value from a specific field and use it in the VBA code again.
I am confused about how to do this and have found CreateQueryDef, but am not sure it is correct to use it.
Confusions:
1. How do I search properly in the database (SQL, seek, find etc statements)
2. How do I set the pointer to THE found record?
3. How do I present the value back in word VBA?
4. If you have any good tips, hints and code I am most greatful!
Have a sunny day!
/Per
This is the code I have been "fiddling with" but it does not work for sure ;-)! (PS! I am Swedish so there might be some strange words here and there)
Sub test_of_query()
'Typ av CV som skall inkluderas
Dim dbJob As database
Dim rstTemp As Recordset
Dim strSQL As String
Set dbJob = OpenDatabase Name:=ActiveDocument.AttachedTemplate.Path + "\job.mdb"
' Open recordsets from the Definitioner table
Set rstDefinitions = dbJob.OpenRecordset("Definitioner", dbOpenDynaset)
'Set qdfDefinitioner = dbJob.CreateQueryDef(
'"TestOfQueryDef", "SELECT * FROM Defintioner WHERE Language =" + ComboBox1.Value + " AND Competency = " + ComboBox2.Value) 'This code is delimited and is the final code I will try to use, but I am running a trial below
strSQL = "SELECT * FROM Defintioner WHERE Language = English AND Competency = Project Management"
Set rstTemp = dbJob.OpenRecordset(strSQL)
Debug.Print rstTemp
dbJob.Close
End Sub
