unique numbers and querying

[Decosta]

I am developing a database that will store information about Electricity meters. Each meter has a MPAn(serial) number that is unique(thus it was made as the primary key).

I want to know how to do the following..


1) I type a number in, eg 123 and save it.It then gets put in the database table.

2) i go to the next record and type in 123 again...This is my problem..Instead of creating a new record,How do I get it to go back to the record that has the unique number 123.

I hope the prolem is clear to see.Any help would be helpful. I was trying to use recordsets, but I'm very clueless aout them at the moment.

[abhijit]

Hi,
My suggestion to you is keep two forms. One for the purposes of displaying records. Use this form for the purpose of navigation.

Now whenever you click on a record, present that record for editing.

Regards,
Abhijit

[Decosta]

This is what I have so far, it is finding and displaying the records. It is even adding a new record into the database table. What it is sometimes doing is creating another record with the same unique number..Is there a way of just editing the record that is already there.

This is the code I have so far.


Option Compare Database
Option Explicit
---------------------------------------------------------

Private Sub cmdEdit_Click()

Dim Db As DAO.Database
Dim rs As DAO.Recordset
Dim strsearch As String

Set Db = CurrentDb
Set rs = Db.OpenRecordset("MpanData", dbOpenDynaset)



strsearch = "MPAN= '" & Me![txtMpan] & "'"
rs.FindFirst (strsearch)


rs.Edit
rs![Mpan] = Me!txtMpan
rs![User] = Me!cboUser
rs![Date] = Me!txtDate
rs![Status] = Me!cboStatus
rs![Information] = Me!txtInformation
rs.Update

Set rs = Nothing
Set Db = Nothing

Forms!frmD170!txtMpan.SetFocus
cmdEdit.Enabled = False
cmdSubmit.Enabled = False

ResetFields
Forms!frmD170!txtMpan.SetFocus

End Sub
-----------------------------------------------------

Private Sub txtMpan_LostFocus()


If Not (IsNull(Me![txtMpan])) Then

Dim Db As DAO.Database
Dim rs As DAO.Recordset
Dim strsearch As String

strsearch = "MPAN= '" & Me![txtMpan] & "'"
Set Db = CurrentDb
Set rs = Db.OpenRecordset("MpanData", dbOpenDynaset)
rs.FindFirst (strsearch)



If (rs.NoMatch) Then

MsgBox "Record not found, enter new record"
cmdSubmit.Enabled = True

Else

cmdEdit.Enabled = True

Me!Mpan = rs![Mpan]
Me!cboUser = rs![User]
Me!txtDate = rs![Date]
Me!cboStatus = rs![Status]
Me!txtInformation = rs![Information]


End If
End If

Set rs = Nothing
Set Db = Nothing

End Sub

-------------------------------------------------------

Private Sub cmdSubmit_Click()

Dim Db As DAO.Database
Dim rs As DAO.Recordset

Set Db = CurrentDb
Set rs = Db.OpenRecordset("MpanData", dbOpenDynaset)

rs.AddNew
rs![Mpan] = Me!txtMpan
rs![User] = Me!cboUser
rs![Date] = Me!txtDate
rs![Status] = Me!cboStatus
rs![Information] = Me!txtInformation
rs.Update

Set rs = Nothing
Set Db = Nothing

ResetFields

cmdSubmit.Enabled = False
Forms!frmD170!txtMpan.SetFocus

End Sub
-----------------------------------------------------

Private Sub Form_Load()

cmdEdit.Enabled = False
cmdSubmit.Enabled = False

End Sub
---------------------------------------------------

Sub ResetFields()

Forms!frmD170!txtMpan.SetFocus
txtMpan.Text = ""

Forms!frmD170!cboUser.SetFocus
cboUser.Text = ""

Forms!frmD170!cboStatus.SetFocus
cboStatus.Text = ""

Forms!frmD170!txtDate.SetFocus
txtDate.Text = ""

Forms!frmD170!txtInformation.SetFocus
txtInformation.Text = ""

End Sub
-------------------------------------------------------------


What modifications can i do to edit the record that is found,so that there is only 1 record and not 2 or more..

Also I get a msgbox that informs me that the data has beenhcanged since i last editted it, how do i work around that so that we can display and edit.

I don't mind it locking if two users are looking at it,but anything would be helpful..