Conditional data entry from the Access database to Excel by vba

[m.vesali]

Hello


I am in macro and vba is a beginner.
I want to use a variable instead of the word “owner”, which is equal to one cell in sheet2.
I did it and got it error.
Can you help me?
Thanks

my code is:

Option Explicit

Sub getDataFromAccess()
‘ Click on Tools, References and select
‘ the Microsoft ActiveX Data Objects 2.0 Library

Dim DBFullName As String
Dim Connect As String, Source As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
Dim Col As Integer

Cells.Clear

‘ Database path info

‘ Your path will be different
DBFullName = “C:\Users\takyar\Documents\NorthWind.accdb”
‘ Open the connection
Set Connection = New ADODB.Connection
Connect = “Provider=Microsoft.ACE.OLEDB.12.0;”
Connect = Connect & “Data Source=” & DBFullName & “;”
Connection.Open ConnectionString:=Connect

‘ Create RecordSet
Set Recordset = New ADODB.Recordset
With Recordset
‘ Filter Data
'Source = “SELECT * FROM Orders WHERE [Shipper ID] = 3 ”
Source = “SELECT * FROM Customers WHERE [Job Title] = ‘Owner’ ”

.Open Source:=Source, ActiveConnection:=Connection

‘ MsgBox “The Query:” & vbNewLine & vbNewLine & Source

‘ Write field names
For Col = 0 To Recordset.Fields.Count – 1
Range(“A1”).Offset(0, Col).Value = Recordset.Fields(Col).Name
Next

‘ Write recordset
Range(“A1”).Offset(1, 0).CopyFromRecordset Recordset
End With
ActiveSheet.Columns.AutoFit
Set Recordset = Nothing
Connection.Close
Set Connection = Nothing
End Sub

[pbaldy]

You got an error with this, or some effort with a variable that you're not showing us? Knowing the error and where it occurs would help. You use a variable would look like:

Source = "SELECT * FROM Customers WHERE [Job Title] = '" & VariableName & "'"

[m.vesali]

When I define a variable: dim factor as integer
And factor is equal to the value of A1 cell in sheet2
The following line encounters an error:
.Open Source: = Source, ActiveConnection: = Connection

[pbaldy]

You still aren't saying what the error is, or shown how Source is populated. You can use this to see what it contains at runtime:

http://www.baldyweb.com/ImmediateWindow.htm

[m.vesali]

This message is displayed when I run the macro
"No value given for one or more required parameters"
And I'm looking for the cause of it

[pbaldy]

And you still haven't shown your code. This worked

DoCmd.OpenForm "EnterData", , , "ID = " & Me.ID

[m.vesali]

This is my code, of course, with a little change over the previous one

Option Explicit

Sub getDataFromAccess()
' Click on Tools, References and select
' the Microsoft ActiveX Data Objects 2.0 Library

Dim DBFullName As String
Dim Connect As String, Source As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
Dim Col As Integer
Dim FACTOR As Integer

FACTOR = Sheets("sheet2").Range("a1").Value

Cells.Clear

' Database path info

' Your path will be different
DBFullName = "E:\LoadExcel.accdb"
' Open the connection
Set Connection = New ADODB.Connection
Connect = "Provider=Microsoft.ACE.OLEDB.12.0;"
Connect = Connect & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Connect

' Create RecordSet
Set Recordset = New ADODB.Recordset
With Recordset
' Filter Data
Source = "SELECT * FROM Customers WHERE [Division] = 'owner'and [Gross Sales] =FACTOR "
'Source = "SELECT * FROM Customers WHERE [Gross Sales] = 100 "

.Open Source:=Source, ActiveConnection:=Connection

' MsgBox "The Query:" & vbNewLine & vbNewLine & Source

' Write field names
For Col = 0 To Recordset.Fields.Count - 1
Range("A1").Offset(0, Col).Value = Recordset.Fields(Col).Name
Next

' Write recordset
Range("A1").Offset(1, 0).CopyFromRecordset Recordset
End With
ActiveSheet.Columns.AutoFit
Set Recordset = Nothing
Connection.Close
Set Connection = Nothing
End Sub

[m.vesali]

I apologize for having taken your time
How should I use this?
DoCmd.OpenForm "EnterData",,,, "ID =" & Me.ID

[pbaldy]

The variable has to be concatenated into the string:

Source = "SELECT * FROM Customers WHERE [Division] = 'owner' and [Gross Sales] = " & FACTOR

Sorry, the code in post 6 was a goof on my part.

[m.vesali]

that was perfect
thanks for your help
Good luck

[pbaldy]

Happy to help and welcome to the site by the way!

[m.vesali]

Sorry
A little question

In the above code, we filtered the data with two conditions and then entered Excel.
Now can we clear the corresponding records from the database with the same two conditions?

[pbaldy]

I'm more used to working from the Access side rather than the Excel side. Basically you execute delete SQL with the same criteria:

"DELETE * FROM Customers WHERE [Division] = 'owner' and [Gross Sales] = " & FACTOR

[m.vesali]

Thankful
I'll try this now

[m.vesali]

Yes you are right
It was perfectly correct
Thank you for your attention