I want to create a search form with ACCESS
Asked 2 years ago, Updated 2 years ago, 99 viewsI would like to create a search form with ACCESS VBA.
(Query - I think we can do the same thing, but this time we want to implement it in VBA.)
As shown in the attached image, there are about 20 tables in the same format that store the distance between each member number and each store.(Tables are divided by chain)
By specifying the conditions, we would like to create a search form that extracts membership numbers that match the conditions.
For example, I would like to be able to specify multiple conditions under and/or conditions, such as "Members with two or more chain A stores within 50km distance and one or more chain B stores within 30km".
I implemented it halfway through with Excel VBA, but due to the large amount of data, I would like to move to ACCESS management.
Would it be possible to create a search form in ACCESS by changing the code below a little?
I've never used ACCESS before, and I'm asking because I'm not familiar with it.
Subtest()
Constfpath As String="D:\Data\"
DimfName As String
Dimwb As Workbook
Dimsh As Worksheet
Dimcname As String
Dimtb1() As Variant, tb2() As Variant
Dim r1 As Long, r2 As Long, c As Long
Dimi As Long, imax As Long
Dim j As Long, jmax As Long
Application.ScreenUpdating=False
Set sh=Works ("Sheet 1")
With sh
If.Range("F4").Value<>"Then
.Range("F4:F" & .Cells(Rows.Count, 6).End(xlUp).Row).ClearContents
End If
End With
ReDimtb1(2,0)
fName=Dir(fpath&"*.xlsx", vbNormal)
Do Until fName=""
Setwb=Workbooks.Open(fpath&fName)
With wb.Works(1)
For1=2 To.Cells(Rows.Count, 1).End(xlUp).Row
Forc=2 To.Cells(1, Columns.Count).End(xlToLeft).Column
tb1(0,UBound(tb1,2))=.Cells(r1,1).Value
tb1(1,UBound(tb1,2)) = Left(fName, Len(fName)-5)
tb1(2,UBound(tb1,2))=.Cells(r1,c).Value
ReDim Preserve tb1(2,UBound(tb1,2)+1)
Next c
Next r1
End With
wb.Close
fName = Dir()
Loop
imax=UBound(tb1,2)-1
r2 = 3
With sh
For1=4 To.Cells (Rows.Count, 2).End(xlUp).Row
If.Cells(r1,3).Value<> "Not Configured" And.Cells(r1,4).Value<>0 Then
Erased tb2
Redimtb2 (10)
Fori=0 Toimax
If tb1(1,i)=.Cells(r1,2).Value And tb1(2,i)<=.Cells(r1,3).Value Then
For j = 0 To UBound(tb2,2)-1
If tb1(0,i) = tb2(0,j) Then
tb2(1,j) = tb2(1,j)+1
End If
Next j
If j>UBound(tb2,2) - 1 Then
tb2(0,UBound(tb2,2))) = tb1(0,i)
tb2(1,UBound(tb2,2)) = 1
ReDim Preserve tb2(1,UBound(tb2,2)+1)
End If
End If
Next i
jmax = UBound(tb2,2)-1
For j = 0 To jmax
If tb2(1,j)>=.Cells(r1,4) Then
r2 = r2+1
Cells(r2,6).Value=tb2(0,j)
End If
Next j
End If
Next r1
US>'Delete
.Range("F4:F" &.Cells(Rows.Count, 6).End(xlUp).Row).RemoveDuplicates Columns:=Array(1), Header:=xlNo
End With
Application.ScreenUpdating=True
End Sub
*This code was implemented with the assumption that the data would be held horizontally, but ACCESS would like to hold it vertically.
1 Answers
Would it be possible to create a search form in ACCESS by changing the code below a little?
I'll start with the conclusion.I can't.
Excel is spreadsheet software and Access is relational database software.
There is no part of the code that can be used in Access.
If you're designing with Access, give up your Excel experience.
I would like to create a search form with ACCESS VBA.
(Query - I think we can do the same thing, but this time we want to implement it in VBA.)
It's already wrong from the beginning.The database performs data operations in SQL.Search (extract) as well.The contents of the query are SQL.Query is a tool that allows people who don't understand SQL to visually design SQL.
And the Access form is basically used in conjunction with the query.
If you use a VBA, you end up working with SQL from the VBA.
You should forget about Excel and start with the basic functions of Access in the introductory book.
Also, table design is important for databases.If you design it like an Excel worksheet, it tends to be useless as a database.You should also learn the basics of database table design.
If you have any answers or tips
© 2024 OneMinuteCode. All rights reserved.