Where do I begin?

[dcorleto]

I have a database of tables. I would like to create one form that will allow me to search a table on a field to see if a records exists. If it does, then display the information and allow the user to edit it if necessary. If the record does not exist, then I would like to give the user the option to add it to the table.

Can someone please advise on some sites or research materials to look at to learn how to accomplish this, or if there are some wizard type addins that will make this easy.

Many thanks in advance for your replies.

 

[jkevergreen]

Here is some code where I use an ado programatic connection. As the form loads it connects to the database and table that I am interested in. In the "PRIVATE SUB cmdOK_click" you can see I used the "Find" method to search for a name in the "Administrators" table defined in the ado Connection as the form is loaded. With regard to adding a name to the table, I used another form to manage Administrators. I will attach that code next.

hope this helps out....



Private Sub Form_Load()
Call CloseMe
'//Programatic ADO Connection

'//Define the properties for the connection object
'//then open the connection.
pconLogin.Mode = adModeShareDenyNone
pconLogin.CursorLocation = adUseClient
pconLogin.Provider = "Microsoft.Jet.OLEDB.4.0" '//4.0 used to connect to Access 2000
pconLogin.ConnectionString = "Persist Security Info = False;" & _
"Data Source = C:\jerry\ISO TOOLS.mdb"
pconLogin.Open

'//Define the properties for the command object.
Set pcmdLogin.ActiveConnection = pconLogin
pcmdLogin.CommandType = adCmdTable
pcmdLogin.CommandText = "Administrators" '//Table used - PLC DATA ACCESS.mdb

'//Define the properties for the recordset object then
'//open the recordset. The same recordset is used
'//while the form is open. Thus, a module level varialble is
'//used.
mrstLogin.LockType = adLockOptimistic
mrstLogin.CursorLocation = adUseClient
mrstLogin.CursorType = adOpenKeyset
mrstLogin.Open pcmdLogin

ReDim mvntBookmark(1) '//Initialize the array of bookmarks


End Sub

Private Sub cmdCancel_Click()

If MsgBox("Are you sure you want to exit" _
& " ADMIN Functions", vbYesNo, "ADMINISTRATION LOGIN&quot = vbYes Then
Call CloseMe
frmCalStandards.Show
Unload frmLogin
Exit Sub

Else
txtUserID.Text = ""
txtPassword.Text = ""
txtUserID.SetFocus '//If no exit then setfocus on usersID
End If

End Sub

Public Function CloseMe()
On Error Resume Next
pconLogin.Close '//used to insure connection is closed as needed for pgm.
'//MsgBox "Your connection has been closed", vbOKOnly
End Function
Private Sub cmdOK_Click()

Static intBadLogins As Integer
Static intBadLoginsPass As Integer
Dim Find As String '//Find Administrator ID
Dim Pass As String '//Find Administrator Password

On Error GoTo ErrorNoLan

Find = txtUserID.Text
If Find = "" Then
MsgBox "Please Enter Your Login ID"
txtUserID.SetFocus
Exit Sub
Else '//in find method use field name within table
mrstLogin.Find "UserID = " _
& "'" & Find & "'"
If mrstLogin.EOF Then
intBadLogins = intBadLogins + 1
If intBadLogins = 3 Then
MsgBox " Opps--- three tries and you're out!"
intBadLogins = 0
txtUserID.Text = ""
txtPassword.Text = ""
'frmCalStandards.Show
Call CloseMe
Unload frmLogin '//Only 3 tries then your outa here
frmISO.Show
Unload frmCalStandards
Exit Sub
Else
MsgBox "The Current Logon ID is incorrect. Please" _
& "Try Again ...", vbOKOnly
txtUserID.Text = ""
txtPassword.Text = ""
txtUserID.SetFocus
mrstLogin.MoveFirst
Exit Sub
End If
End If
End If

Pass = txtPassword.Text
If Pass = "" Then
MsgBox "Please Enter Your Logon Password"
txtPassword.SetFocus
Exit Sub
Else '//in find method use field name within table
mrstLogin.Find "Password = " _
& "'" & Pass & "'"
If mrstLogin.EOF Then
intBadLoginsPass = intBadLoginsPass + 1
If intBadLoginsPass = 3 Then
MsgBox " Opps--- three tries and you're out!"
intBadLoginsPass = 0
txtUserID.Text = ""
txtPassword.Text = ""
frmCalStandards.Show
Call CloseMe
Unload frmLogin '//Only 3 tries then your outa here
Exit Sub
Else
MsgBox "The Current Password is incorrect. Please" _
& "Try Again ...", vbOKOnly
txtPassword.Text = ""
txtPassword.SetFocus
mrstLogin.MoveFirst
Exit Sub
End If
End If
End If


txtUserID.Text = ""
txtPassword.Text = ""

frmCalStandards.txtName1.Locked = False
frmCalStandards.txtName2.Locked = False
frmCalStandards.txtID1.Locked = False
frmCalStandards.txtID2.Locked = False
frmCalStandards.txtCertificate1.Locked = False
frmCalStandards.txtCertificate2.Locked = False
frmCalStandards.txtCalDate1.Locked = False
frmCalStandards.txtCalDate2.Locked = False
frmCalStandards.txtCalDue1.Locked = False
frmCalStandards.txtCalDue2.Locked = False

Call CloseMe

frmCalStandards.Show
Unload frmLogin



Exit Sub

ErrorNoLan:
MsgBox "Unable to connect to the Database used to verify" _
& " Administrators,... Please try again later. ", vbOKOnly, "ADMINISTRATION LOGIN"
Unload frmLogin
End Sub

 

[jkevergreen]

Continued:

Using another form I again made an ado connection to the table of interest. Athough I am not sure how to send you the form itself, if you read down through this code you can see the various cmd buttons that I used to ADD, Delete, or Update new users. I know this is alot of code and may not be exactly what you were looking for but maybe it will provide you with some ideas.

Good Luck....




Private Sub Form_Load()

'//Define the properties for the connection object
'//then open the connection.
pconLogin.Mode = adModeShareDenyNone
pconLogin.CursorLocation = adUseClient '//3.5.1 used for Access 97 & older
pconLogin.Provider = "Microsoft.Jet.OLEDB.4.0" '//4.0 used to connect to Access 2000
pconLogin.ConnectionString = "Persist Security Info = False;" & _
"Data Source = C:\IE\PLC DATA ACCESS.mdb"
pconLogin.Open

'//Define the properties for the command object.
Set pcmdLogin.ActiveConnection = pconLogin
pcmdLogin.CommandType = adCmdTable
pcmdLogin.CommandText = "Administrators" '//Name of table used

'//Define the properties for the recordset object then
'//open the recordset. The same recordset is used
'//while the form is open. Thus, a module level varialble is
'//used.
mrstLogin.LockType = adLockOptimistic
mrstLogin.CursorLocation = adUseClient
mrstLogin.CursorType = adOpenKeyset
mrstLogin.Open pcmdLogin
'//MsgBox "connection open"
'//Initialize the array of bookmarks
ReDim mvntBookmark(1)

'Set the initial editing state so that a record is not
'being edited.

'Display the current record identified by the current record
'Pointer.
Call LoadCurrentRecord

End Sub

Private Sub LoadCurrentRecord()

If mrstLogin.EOF Then
mrstLogin.MoveLast
Else
End If
If mrstLogin.BOF Then
mrstLogin.MoveFirst
Else
End If
txtUserID = mrstLogin("userid&quot
txtPassword = mrstLogin("password&quot
End Sub

Private Sub cmdMoveNext_Click()

If txtUserID.Text = "" Then
MsgBox "Please finish your present update.", vbOKOnly, "ADMINISTRATOR UPDATES"
Exit Sub
Else
If txtPassword.Text = "" Then
MsgBox "Please finish your present" _
& " update.", vbOKOnly, "ADMINISTRATOR UPDATES"
Exit Sub
Else
End If

End If
mrstLogin.MoveNext
Call LoadCurrentRecord
End Sub


Private Sub cmdMovePrevious_Click()

If txtUserID.Text = "" Then
MsgBox "Please finish your present update.", vbOKOnly, "ADMINISTRATOR UPDATES"
Exit Sub
Else
If txtPassword.Text = "" Then
MsgBox "Please finish your present" _
& " update.", vbOKOnly, "ADMINISTRATOR UPDATES"
Exit Sub
Else
End If

End If
mrstLogin.MovePrevious
Call LoadCurrentRecord
End Sub

Private Sub cmdAdd_Click()
'//Is the user adding a record already?
If mrstLogin.EditMode = True Then
MsgBox "Please finish your updates before adding a" _
& " new record", vbOKOnly, "ADMINISTRATOR UPDATES"
Exit Sub
Else
End If
If txtUserID.Text = "" Then
MsgBox "Please finish your present update.", vbOKOnly, "ADMINISTRATOR UPDATES"
Exit Sub
Else
If txtPassword.Text = "" Then
MsgBox "Please finish your present" _
& " update.", vbOKOnly, "ADMINISTRATOR UPDATES"
Exit Sub
Else
End If

End If

mrstLogin.AddNew
txtUserID.SetFocus
txtUserID = ""
txtPassword = ""
End Sub

Private Sub cmdUpdate_Click()

If (txtUserID = "&quot Or (txtPassword = "&quot Then
mrstLogin.CancelUpdate
MsgBox "Edit not complete, Canceling Update", vbOKOnly, "ADMINISTRATOR UPDATES"
mrstLogin.MoveFirst
Call LoadCurrentRecord
Exit Sub
Else
End If
If mrstLogin.EditMode Then
mrstLogin.Fields(0) = txtUserID.Text '//add these values to a
mrstLogin.Fields(1) = txtPassword.Text '//new record.

mrstLogin.Update '//If in the ADD mode, database will be updated.
MsgBox "Your changes were recorded", vbOKOnly, "ADMINISTRATOR UPDATES"
Exit Sub
Else
End If
MsgBox "No changes were recorded", vbOKOnly, "ADMINISTRATOR UPDATES"
End Sub

Private Sub cmdCancel_Click()

If mrstLogin.EditMode Then
mrstLogin.CancelUpdate
mrstLogin.MoveFirst
Call LoadCurrentRecord
Exit Sub
Else
mrstLogin.CancelUpdate
mrstLogin.MoveFirst
Call LoadCurrentRecord
End If
End Sub

Private Sub cmdDelete_Click()
Dim intResult As Integer
On Error GoTo ErrorHandler

If mrstLogin.EditMode Then
MsgBox "You are currently in the Edit mode, this function" _
& " is not available.", vbOKOnly, "ADMINISTRATOR DELETE"
Exit Sub
End If

intResult = MsgBox("Are you sure you want to delete an Administrator record?", _
vbYesNo, "Delete an Administrator Record?&quot

If intResult = vbYes Then '//used to remove a user from the database
mrstLogin.Delete
mrstLogin.MovePrevious
Call LoadCurrentRecord
MsgBox "The selected record has been deleted" _
& vbOKOnly, "ADMINISTRATOR DELETE"
Else
End If

Exit Sub

ErrorHandler:

Select Case Err.Number
Case 3200 '//Referential Integrity Check
MsgBox " You cannot delete this record", vbOKOnly, "ADMINISTRATOR DELETE"
Exit Sub
Case Else
MsgBox "Unexpected error, (frmAdministrators-cmdDelete)Contact Your PLC" _
& " Administrator", vbOKOnly, "ADMINISTRATOR DELETE"
Resume Next
End Select

End Sub

Private Sub cmdExit_Click()

If mrstLogin.EditMode Then '//If in Edit mode msgbox, else
MsgBox "Please finish your updates or press Cancel before" _
& " closing the form", vbOKOnly, "ADMINISTRATOR UPDATES" '//show frmRecords.
Exit Sub
Else
End If

mrstLogin.CancelUpdate
mrstLogin.MoveFirst
Call LoadCurrentRecord
Call CloseMe
'frmADMIN.Show '//on exit shows frmRecords and hides the frmUsers
Unload frmAdministrators

End Sub

Public Function CloseMe()
pconLogin.Close '//used to insure connection is closed as needed for pgm.
'//MsgBox "Your connection has been closed", vbOKOnly
End Function

Private Sub txtPassword_KeyPress(KeyAscii As Integer)

If KeyAscii >= 97 And KeyAscii <= 122 Then '//converts password to upper case
KeyAscii = KeyAscii - 32
End If
End Sub
Private Sub txtUserID_KeyPress(KeyAscii As Integer)

If KeyAscii >= 97 And KeyAscii <= 122 Then '//converts password to upper case
KeyAscii = KeyAscii - 32
End If
End Sub

 

[jkevergreen]

One thing I forgot to mention. To use this type of ado connection you will need to declare these variables either in an Public Module or on the form itselft. Remember to Close the ado connection when you finsh one task and then re-open it when you begin another. If not, you will encounter errors.

Jkevergreen@yahoo.com.....