P
Pam
I have a problem I can't figure out, and I am seeking your help. I have a
macro that queries an Access database. This part of it runs fine the first
time. The second time it jumps to the combobox code and I can't figure out
why. (I noted where in jumps in the code below). I am not that
knowledgeable in programming and have muddled my way through so far with a
little help from a programmer in getting the code for the Access part, but
even in researching this newsgroup, I can't figure this out. Can you help me?
Thank You, Pam
Sub ProjectData2()
'Finds all data (Used to provide all project numbers to dropdown values)
Dim sWHERE As String
Dim sAccessFile As String
Dim sTable As String
Dim rowx As String
Dim filterrng As String
'Clears data in rows a-j in "Sample" worksheet
Sheets("Sample").Select
Range("A2:j2").Select
Selection.ClearContents
sAccessFile = ThisWorkbook.Path & "\projectestimationdraft1.mdb"
sTable = "All Fields"
' Get rid of existing data in the table starting at row 5
ClearTableData2 5
' Construct a query using data in the table starting at row 2
ConstructQuery2 1, sWHERE
' Get query data from Access and put it in the table starting at row 5
QueryAccessToDataTable2 5, sAccessFile, sTable, sWHERE
'Filters data and updates dropdown values of project numbers
Worksheets("Sample").Select
Range("A6").Select
Range(Selection, Selection.End(xlDown)).Select
filterrng = Selection.Address
Range(filterrng).AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=Range( _
"BA6"), Unique:=True
Sheets("Dropdown Values").Select
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents 'it jumps to my combo box
right after here
Sheets("Sample").Select
Range("bA6").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
Sheets("Dropdown Values").Select
Range("A2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
rowx = Selection.End(xlDown).row
With ActiveWorkbook.Names("ProjectNo")
.Name = "ProjectNo"
.RefersToR1C1 = "='Dropdown Values'!R2C1:R" & rowx & "C1"
.Comment = ""
End With
Sheets("Estimate").Select
End Sub
macro that queries an Access database. This part of it runs fine the first
time. The second time it jumps to the combobox code and I can't figure out
why. (I noted where in jumps in the code below). I am not that
knowledgeable in programming and have muddled my way through so far with a
little help from a programmer in getting the code for the Access part, but
even in researching this newsgroup, I can't figure this out. Can you help me?
Thank You, Pam
Sub ProjectData2()
'Finds all data (Used to provide all project numbers to dropdown values)
Dim sWHERE As String
Dim sAccessFile As String
Dim sTable As String
Dim rowx As String
Dim filterrng As String
'Clears data in rows a-j in "Sample" worksheet
Sheets("Sample").Select
Range("A2:j2").Select
Selection.ClearContents
sAccessFile = ThisWorkbook.Path & "\projectestimationdraft1.mdb"
sTable = "All Fields"
' Get rid of existing data in the table starting at row 5
ClearTableData2 5
' Construct a query using data in the table starting at row 2
ConstructQuery2 1, sWHERE
' Get query data from Access and put it in the table starting at row 5
QueryAccessToDataTable2 5, sAccessFile, sTable, sWHERE
'Filters data and updates dropdown values of project numbers
Worksheets("Sample").Select
Range("A6").Select
Range(Selection, Selection.End(xlDown)).Select
filterrng = Selection.Address
Range(filterrng).AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=Range( _
"BA6"), Unique:=True
Sheets("Dropdown Values").Select
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents 'it jumps to my combo box
right after here
Sheets("Sample").Select
Range("bA6").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
Sheets("Dropdown Values").Select
Range("A2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
rowx = Selection.End(xlDown).row
With ActiveWorkbook.Names("ProjectNo")
.Name = "ProjectNo"
.RefersToR1C1 = "='Dropdown Values'!R2C1:R" & rowx & "C1"
.Comment = ""
End With
Sheets("Estimate").Select
End Sub