navigate to a record and edit or replace it

[graccess]

I have some code that performs a lookup on a particular field and then prompts me if I wish to edit its entire record. That's where I'm running into difficulty. I should only have one entry in my table after selecting ok to overwrite record but it keeps adding the record dupes.



Here's what I got so far:

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("companytable", dbOpenDynaset)
If rs.RecordCount <> 0 Then
With rs
.FindFirst "Ticker" = Me.txtTicker
If Not .NoMatch Then
.Edit
.Fields("Company") = Me.txtCompany
.Update
End If
End With
End If


End If



thanks in advance!

[June7]

Why are you trying to edit directly on table and not in a form bound to table? Use code to go to record on form.

Why would you change the company name in CompanyTable?

Want to provide db for analysis? Follow instructions at bottom of my post.

[graccess]

Still debating how to bring that information into the table but assuming I set it up for a form to input entries, if there's an error or typo on the company name, I'd like the ability to append that info. If I only edit the form, wouldn't I end up with multiple entries in my table?

[June7]

I understand if the company name is wrong, wanting to correct in the CompanyTable. So what does the 'ticker' ID have to do with finding the company record? Going in circles. Don't understand data structure and relationships.

[graccess]

the way i thought about checking for a duplicate record based on the users entry is by cross referencing the ticker. i will require a ticker entered as part of the user form and if that ticker already exists in the database, i will prompt that the record already exists and if I want to edit the entire record. over the long term i don't forsee this table changing much but in the beginning when i'm first building the database, this table will store all the company information. from there i plan on relating this table to a more detailed table that will hold multiple records for each respective company/ticker, like notes for each company over time. that make sense?

it's possible i should be using a query to perform this task of adding and editing companies to the table instead of procedure code within the form?

[June7]

Do you have a 'master' table of company info where each record is unique? What is the 'ticker' table for? Are there multiple ticker records for each company? Should only store the company ID in ticker records. If the company name is misspelled, then correct it in the 'master' table and the edit will be reflected in all related records in ticker table when tables are joined in a query. Also, any combobox based on the master table as a RowSource will reflect the edit.

[graccess]

Ticker is not a table, it's a field in the 'company' table. There are multiple tickers and companies stored in the table but only one company per ticker and vice versa.

But you make a good point in that it's probably rare that I would be updating a company spelling in the database and therefore it's probably just easier going into the table directly. But in general, if I were to update a particular record... Is it more efficient/proper to run a query for editing a record or use a procedure in the actual form and edit a previous record from there and update the table that way and was the code I used above on the right track?
Thanks.

[June7]

If the company name is in the table for many records, then all records would have to be edited to correct the spelling. What if the name has been misspelled 10 different ways?

The data structure does not seem normalized.

I am not sure why the code is not working as expected. Are you saying the ticker value gets duplicated?

[graccess]

That's correct. The ticker value is getting duplicated and showing up in the company table as multiple records. In general, the structure I'm developing will be a search for the ticker and that will populate a main form. From there I will have a button to launch a more detailed form where the bulk of the editing and adding of records to a separate table will take place. This table, let's call that 'notes' table will be related to the 'company' table so I will not need to constantly add the tickers and company names, I'll just select the ticker and have the ability to add many notes per ticker. Make sense?

[June7]

Sounds reasonable in theory. Again, do you want to provide db for analysis?

[graccess]

Mostly for using it as a repository for notes and various data fields. That being said, should my search and edit procedures get performed by query or my current approach which is procedure code in my entry forms?
Thx

[June7]

Whether query is involved or not, will probably need code, either macro or VBA, in form event. Again, I don't know why your code isn't working. Don't know enough about your database.