Trying to create a parking lot database that is editable through a multipage user form. Since 1 parking spot can have up to 3 different vehicles parked in it, I'm trying to figure out how to be able to search multiple columns at once.
This is the original layout. i.e. I need to be able to search for a license plate or fragment of a plate across the entire DB. But everything I've researched says the search will find the first instance, then isolate the search to that column.
<tbody>
</tbody>
So my workaround is to enter the data for one parking space on multiple rows:
<tbody>
</tbody>
<tbody>
</tbody>
<tbody>
</tbody>
<tbody>
</tbody>
The code I have for this is:
The problem is now that I've added the IF statement for
It's not going to the bottom of the sub to
My questions:
1. How do I search/filter multiple columns of entry? (I can post what I have for the filter/search, but basically it searches and copy's the data to another part of the SS into a named area and then puts that data into a listbox in the userform.)
or
2. Why is it not going to the Clear and msgbox at the end of the routine?
Thank you in advance
This is the original layout. i.e. I need to be able to search for a license plate or fragment of a plate across the entire DB. But everything I've researched says the search will find the first instance, then isolate the search to that column.
| Lot | Space | Sched | Last | First | ID | Grade | V1 Year | V1 Color | V1 Make | V1 Model | V1 Lic. Plate | V2 Year | V2 Color | V2 Make | V2 Model | V2 Lic. Plate | V3 Year | V3 Color | V3 Make | V3 Model | V3 Lic. Plate |
| Gold | 440 | Doe | John | 1234 | 12 | 2007 | Grey | Honda | Odyssey | ABC1234 | 2006 | Blue | Chrysler | PT Cruiser | DEF2345 | 2016 | Grey | Dodge | Journey | GHI7689 |
<tbody>
</tbody>
So my workaround is to enter the data for one parking space on multiple rows:
| Lot | Space | Sched | Last | First | ID | Grade | V1 Year | V1 Color | V1 Make | V1 Model | V1 Lic. Plate |
<tbody>
</tbody>
| Gold | 440 | Doe | John | 1234 | 12 | 2007 | Grey | Honda | Odyssey | ABC1234 |
<tbody>
</tbody>
| Gold | 440 | Doe | John | 1234 | 12 | 2006 | Blue | Chrysler | PT Cruiser | DEF2345 |
<tbody>
</tbody>
| Gold | 440 | Doe | John | 1234 | 12 | 2016 | Grey | Dodge | Journey |
<tbody> </tbody> |
<tbody>
</tbody>
The code I have for this is:
Code:
Private Sub cmdAddStudent_Click()
Dim DataSH As Worksheet
Dim Addme As Range
Set DataSH = Sheet1
On Error GoTo errHandler:
Set Addme = DataSH.Cells(Rows.Count, 4).End(xlUp).Offset(1, 0)
Application.ScreenUpdating = False
If Me.txtLast = "" Or Me.txtfirst = "" Or Me.txtStudentID = "" Then
MsgBox "There is insufficient data, Please return and add the needed information"
Exit Sub
End If
With DataSH
Addme.Value = Me.txtLast
Addme.Offset(0, 1).Value = Me.txtfirst
Addme.Offset(0, 2).Value = Me.txtStudentID.Value
If obSenior.Value = True Then
Addme.Offset(0, 3).Value = "12"
ElseIf obJunior.Value = True Then
Addme.Offset(0, 3).Value = "11"
ElseIf obSoph.Value = True Then
Addme.Offset(0, 3).Value = "10"
Else
Addme.Offset(0, 3).Value = ""
End If
If obRegular.Value = True Then
Addme.Offset(0, -1).Value = "Regular"
ElseIf obEVIT.Value = True Then
Addme.Offset(0, -1).Value = "EVIT"
ElseIf obER.Value = True Then
Addme.Offset(0, -1).Value = "ER"
ElseIf obLS.Value = True Then
Addme.Offset(0, -1).Value = "LS"
Else
Addme.Offset(0, -1).Value = ""
End If
Addme.Offset(0, 4).Value = Me.cboYear1
Addme.Offset(0, 5).Value = Me.cboColor1
Addme.Offset(0, 6).Value = Me.cboMake1
Addme.Offset(0, 7).Value = Me.cboModel1
Addme.Offset(0, 8).Value = Me.txtLicPlate1
[SIZE=4] [COLOR=#ff0000] If Me.cboYear2 = "" Then
Exit Sub[/COLOR][/SIZE]
Else
Addme.Offset(1, 0) = Me.txtLast
Addme.Offset(1, 1).Value = Me.txtfirst
Addme.Offset(1, 2).Value = Me.txtStudentID.Value
If obSenior.Value = True Then
Addme.Offset(1, 3).Value = "12"
ElseIf obJunior.Value = True Then
Addme.Offset(1, 3).Value = "11"
ElseIf obSoph.Value = True Then
Addme.Offset(1, 3).Value = "10"
Else
Addme.Offset(1, 3).Value = ""
End If
If obRegular.Value = True Then
Addme.Offset(1, -1).Value = "Regular"
ElseIf obEVIT.Value = True Then
Addme.Offset(1, -1).Value = "EVIT"
ElseIf obER.Value = True Then
Addme.Offset(1, -1).Value = "ER"
ElseIf obLS.Value = True Then
Addme.Offset(1, -1).Value = "LS"
Else
Addme.Offset(1, -1).Value = ""
End If
Addme.Offset(1, 4).Value = Me.cboYear2
Addme.Offset(1, 5).Value = Me.cboColor2
Addme.Offset(1, 6).Value = Me.cboMake2
Addme.Offset(1, 7).Value = Me.cboModel2
Addme.Offset(1, 8).Value = Me.txtLicPlate2
End If
[SIZE=4] [COLOR=#ff0000] If Me.cboYear3 = "" Then
Exit Sub[/COLOR][/SIZE]
Else
Addme.Offset(2, 0) = Me.txtLast
Addme.Offset(2, 1).Value = Me.txtfirst
Addme.Offset(2, 2).Value = Me.txtStudentID.Value
If obSenior.Value = True Then
Addme.Offset(2, 3).Value = "12"
ElseIf obJunior.Value = True Then
Addme.Offset(2, 3).Value = "11"
ElseIf obSoph.Value = True Then
Addme.Offset(2, 3).Value = "10"
Else
Addme.Offset(2, 3).Value = ""
End If
If obRegular.Value = True Then
Addme.Offset(2, -1).Value = "Regular"
ElseIf obEVIT.Value = True Then
Addme.Offset(2, -1).Value = "EVIT"
ElseIf obER.Value = True Then
Addme.Offset(2, -1).Value = "ER"
ElseIf obLS.Value = True Then
Addme.Offset(2, -1).Value = "LS"
Else
Addme.Offset(2, -1).Value = ""
End If
Addme.Offset(2, 4).Value = Me.cboYear3
Addme.Offset(2, 5).Value = Me.cboColor3
Addme.Offset(2, 6).Value = Me.cboMake3
Addme.Offset(2, 7).Value = Me.cboModel3
Addme.Offset(2, 8).Value = Me.txtLicPlate3
End If
End With
Exit Sub
Clear
MsgBox "Your parking data was successfully added"
On Error GoTo 0
Exit Sub
errHandler:
MsgBox "Error " & Err.Number & _
" (" & Err.Description & ")in procedure cmdClear_Click of Form ParkinglotDB"
End SubThe problem is now that I've added the IF statement for
Code:
If Me.cboYear2 = "" Then[INDENT]Exit Sub[/INDENT]
ElseIt's not going to the bottom of the sub to
Code:
Clear
MsgBox "Your parking data was successfully added"My questions:
1. How do I search/filter multiple columns of entry? (I can post what I have for the filter/search, but basically it searches and copy's the data to another part of the SS into a named area and then puts that data into a listbox in the userform.)
or
2. Why is it not going to the Clear and msgbox at the end of the routine?
Thank you in advance
