Search problems in a form, and an AutoNumber question

ReaperFett
04-14-2004, 10:25 AM
First, the problem with search. I created a table using the VB form wizard and created a textbox and button for a search. The idea is that when you press the button the member table is searched in the surname field for the text entered in the bar (Wildcards either side). These results then come out where the form is presently. My present code relevent to this search is:
Private Sub SearchMembers_Click()
db.CursorLocation = adUseClient
adoPrimaryRS.Open "select MemberID,Forename,Surname,Street,Town,County,Postcode,PhoneNumber,Emai l from Member where Surname like '*" & MemberSearch.Text & "*' Order by MemberID", db, adOpenStatic, adLockOptimistic
Set grdDataGrid.DataSource = adoPrimaryRS
End Sub
However when I enter data and press search, I get the error "Run-time 424: Object required", highlighting "db.CursorLocation = adUseClient". But simply removing this line causes another error. What am I doing wrong?


My second question reguards AutoNumber from MS Access. I use this because every member needs a unique ID, and it has to be in the leftmost column to allow us to see it immediately. The problem comes because the AutoNumber doesn't automatically add itself. Upon pressing the add member button, a blank line is added to the table at the bottom and the cursor points to MemberID, which is the autonumber. If you click away an error message comes up saying that you can't have an empty row and keeps doing this until you input something. Although annoying, this isn't the big problem. That comes if you input a MemberID which already exists. Upon clicking away, it says that this is not possible because of the repetition. If you then were to simply delete the offending value it then refuses to continue because you have tried to use a null value, forcing the user to try and think of an available number. Also, if you were to click on the exit to main menu button or close the program totally it would warn you, but after it has closed. This would mean that if you entered a whole record with a repetition and then automatically hit return to main menu, you would lose that data.

So basically, is there a way to avoid this? Having the cursor in the second column instead of the first would possibly be enough, but I can't think how to do this.




Thanks in advance.




The code for the page:
Dim WithEvents adoPrimaryRS As ADODB.Recordset
Dim mbChangedByCode As Boolean
Dim mvBookMark As Variant
Dim mbEditFlag As Boolean
Dim mbAddNewFlag As Boolean
Dim mbDataChanged As Boolean

Private Sub Form_Load()
Dim db As ADODB.Connection
Set db = New ADODB.Connection
db.CursorLocation = adUseClient
db.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\library.mdb;"

Set adoPrimaryRS = New ADODB.Recordset
adoPrimaryRS.Open "select MemberID,Forename,Surname,Street,Town,County,Postcode,PhoneNumber,Emai l from Member Order by MemberID", db, adOpenStatic, adLockOptimistic

Set grdDataGrid.DataSource = adoPrimaryRS


mbDataChanged = False
End Sub

Private Sub Form_Resize()
On Error Resume Next
'This will resize the grid when the form is resized
grdDataGrid.Height = Me.ScaleHeight - 30 - picButtons.Height - picStatBox.Height
lblStatus.Width = Me.Width - 1500
cmdNext.Left = lblStatus.Width + 700
cmdLast.Left = cmdNext.Left + 340
End Sub

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
If mbEditFlag Or mbAddNewFlag Then Exit Sub

Select Case KeyCode
Case vbKeyEnd
cmdLast_Click
Case vbKeyHome
cmdFirst_Click
Case vbKeyUp, vbKeyPageUp
If Shift = vbCtrlMask Then
cmdFirst_Click
Else
cmdPrevious_Click
End If
Case vbKeyDown, vbKeyPageDown
If Shift = vbCtrlMask Then
cmdLast_Click
Else
cmdNext_Click
End If
End Select
End Sub

Private Sub Form_Unload(Cancel As Integer)
Screen.MousePointer = vbDefault
End Sub

Private Sub adoPrimaryRS_MoveComplete(ByVal adReason As ADODB.EventReasonEnum, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
'This will display the current record position for this recordset
lblStatus.Caption = "Record: " & CStr(adoPrimaryRS.AbsolutePosition)
End Sub

Private Sub adoPrimaryRS_WillChangeRecord(ByVal adReason As ADODB.EventReasonEnum, ByVal cRecords As Long, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
'This is where you put validation code
'This event gets called when the following actions occur
Dim bCancel As Boolean

Select Case adReason
Case adRsnAddNew
Case adRsnClose
Case adRsnDelete
Case adRsnFirstChange
Case adRsnMove
Case adRsnRequery
Case adRsnResynch
Case adRsnUndoAddNew
Case adRsnUndoDelete
Case adRsnUndoUpdate
Case adRsnUpdate
End Select

If bCancel Then adStatus = adStatusCancel
End Sub

Private Sub cmdAdd_Click()
On Error GoTo AddErr
adoPrimaryRS.MoveLast
adoPrimaryRS.AddNew
grdDataGrid.SetFocus

Exit Sub
AddErr:
MsgBox Err.Description
End Sub

Private Sub cmdDelete_Click()
Reply = MsgBox("Are you sure you wish to delete this member?", 1, "Delete?")
If Reply = 1 Then
On Error GoTo DeleteErr
With adoPrimaryRS
.Delete
.MoveNext
If .EOF Then .MoveLast
End With
Exit Sub
DeleteErr:
MsgBox Err.Description
End If
End Sub




Private Sub cmdFirst_Click()
On Error GoTo GoFirstError

adoPrimaryRS.MoveFirst
mbDataChanged = False

Exit Sub

GoFirstError:
MsgBox Err.Description
End Sub

Private Sub cmdLast_Click()
On Error GoTo GoLastError

adoPrimaryRS.MoveLast
mbDataChanged = False

Exit Sub

GoLastError:
MsgBox Err.Description
End Sub

Private Sub cmdNext_Click()
On Error GoTo GoNextError

If Not adoPrimaryRS.EOF Then adoPrimaryRS.MoveNext
If adoPrimaryRS.EOF And adoPrimaryRS.RecordCount > 0 Then
Beep
'moved off the end so go back
adoPrimaryRS.MoveLast
End If
'show the current record
mbDataChanged = False

Exit Sub
GoNextError:
MsgBox Err.Description
End Sub

Private Sub cmdPrevious_Click()
On Error GoTo GoPrevError

If Not adoPrimaryRS.BOF Then adoPrimaryRS.MovePrevious
If adoPrimaryRS.BOF And adoPrimaryRS.RecordCount > 0 Then
Beep
'moved off the end so go back
adoPrimaryRS.MoveFirst
End If
'show the current record
mbDataChanged = False

Exit Sub

GoPrevError:
MsgBox Err.Description
End Sub

Private Sub SetButtons(bVal As Boolean)
cmdAdd.Visible = bVal
cmdDelete.Visible = bVal
cmdNext.Enabled = bVal
cmdFirst.Enabled = bVal
cmdLast.Enabled = bVal
cmdPrevious.Enabled = bVal
End Sub

Private Sub MemberSearch_KeyPress(KeyAscii As Integer)
If KeyAscii > 96 And KeyAscii < 123 Then
KeyAscii = KeyAscii - 32
End If
End Sub

Private Sub ReturnToStart_Click()
Load LibrarianMenu
Unload LibMembers
LibrarianMenu.Show
End Sub


Private Sub grdDataGrid_KeyPress(KeyAscii As Integer)
If KeyAscii > 96 And KeyAscii < 123 Then
KeyAscii = KeyAscii - 32
End If
End Sub

Private Sub SearchMembers_Click()
db.CursorLocation = adUseClient
adoPrimaryRS.Open "select MemberID,Forename,Surname,Street,Town,County,Postcode,PhoneNumber,Emai l from Member where Surname like '*" & MemberSearch.Text & "*' Order by MemberID", db, adOpenStatic, adLockOptimistic
Set grdDataGrid.DataSource = adoPrimaryRS
End Sub

reboot
04-14-2004, 11:15 AM
db is private to Form_Load, it can't be seen in SearchMembers_Click.

ReaperFett
04-14-2004, 11:30 AM
So I need to copy all the references to db from Form_Load into SearchMembers_Click?


EDIT - I tried putting it all there above the rest of the code in there, and now when I press search it says "Run-time error '3705': Operation is not allowed when the object is open, highlighting "adoPrimaryRS.Open "select MemberID,Forename,Surname,Street,Town,County,Postcode,PhoneNumber,Emai l from Member where Surname like '*" & MemberSearch.Text & "*' Order by MemberID", db, adOpenStatic, adLockOptimistic" when I press debug.

reboot
04-14-2004, 12:21 PM
Dim WithEvents adoPrimaryRS As ADODB.Recordset
Dim mbChangedByCode As Boolean
Dim mvBookMark As Variant
Dim mbEditFlag As Boolean
Dim mbAddNewFlag As Boolean
Dim mbDataChanged As Boolean
Dim db As ADODB.Connection '<-

Private Sub Form_Load()
......

ReaperFett
04-14-2004, 12:47 PM
Okay, by doing that and then moving some things into Private Sub SearchMembers_Click(), it now sortof works. Sortof, meaning nothing comes up in the search :)

Any ideas where I've screwed up?

ReaperFett
04-14-2004, 02:59 PM
Slight change to my second problem now. I changed:

Private Sub adoPrimaryRS_MoveComplete(ByVal adReason As ADODB.EventReasonEnum, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
'This will display the current record position for this recordset
lblStatus.Caption = "Record: " & CStr(adoPrimaryRS.AbsolutePosition)
End Sub

So now, the MemberID will appear in this label, meaning in theory I don't need to display MemberID any more. I tried removing MemberID from the line "adoPrimaryRS.Open "select MemberID,Forename....." in Private Sub Form_Load(), but that brings back an error. Where did I go wrong?

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum