Database

[Decosta]

I need to develop a app for work where I need to enter values into a table.

When the user enters a data into the fields, the table is updated.
What do I have to do if

I type in an number that I have used before.It looks in the table to see if its there, if it is, all the data previously entered is displayed on the form, if the previous number is not there. You can add a new record.

Hope this makes sense...I've tried various ways, but I am stuck...

I would be grateful if you could post some sample code to get me started. Thanks

[PilgrimPete]

How far have you got?

What database are you using? Are you writing a VB front-end? Are you using ADO?

Sorry to answer your question with more of my own, but you'll get a better response from a more focused question...

[Ecniv]

I was going to suggest a way using DAO or ADO and code, but only the steps.


1) On a Search or update button, do the following
a) Open the tables via an sql statement filtering on the inputted value
b) decide whether you need to retrieve the results (in which case you have the data now and populate the form) or create a new record (do you have to?)
c) notify the user/refresh the form perhaps

2) On the save records details click
a) Open up the recordset using the sql statement again
b) If it exists - edit and update **
c) If it doesn't exist - create and update

** Depends how you are letting everyone use the database as to whether a record gets created before the current user saves and whether you need a pop up form to ask the user whether they want to update the record or cancel...

As Pete said though, more specific the problem the easier it is to help. Also, not everyone will happily give their code up - its better that you code first, get some problem and find a solution (either by yourself (Note: this usually happens just after you post - if that does happen - post up your solution) or on here)


Vince

[Decosta]

This is what I have so far. Its finding the record, and updating it, but its adding a new record instead of updating the record it has found.


Where am I going wrong?


Private Sub Command31_Click()
Dim dbs As Database, rst As Recordset
Dim sql As String

' Return reference to current database.
Set dbs = CurrentDb
sql = "SELECT * FROM d170 WHERE MPAN = '2326384960017'"
Set rst = dbs.OpenRecordset(sql)

' Create dynaset-type Recordset object.
'Set rst = dbs.OpenRecordset("d170", dbOpenDynaset)
' Find first occurrence.


rst.MoveFirst

'rst.FindFirst (sql)
' Loop until no matching records.
'rst.NoMatch = True

'Do Until rst.NoMatch
If Not rst.NoMatch Then

With rst
.Edit ' Enable editing.
!MPAN = Me!MPAN ' Change title.
!User = Me!User
!Status = Me!Status
!Additional_Info = Me!Additional_Info
.Update ' Save changes.
'.FindNext sql ' Find next occurrence.
End With
Else
With rst
.AddNew
!MPAN = Me.MPAN ' Change title.
!User = Me.Name
!Status = Me.Status
!Additional_Info = Me!Additional_Info
.Update ' Save changes.
'.FindNext sql ' Find next occurrence.
End With
rst.Close
Set dbs = Nothing
End If
'Loop

End Sub

[Memnoch1207]

you may need to add an SQL update statement to your code.