Editing a non-duplicating table

[PharmaFun]

Hi there,

I have a table tied to a form. In the form, I can pull up an entry with a combo box that fills all the fields. However, if I try to edit the fields and then re-save, it says duplicates not allowed. Well I set the table data to avoid duplicate entries, but I do want to be able to edit and overwrite a previous entry.

How can I do that?

Thanks,
Dean

 

[GingerR]

what you want to do is an ordinary every-day thing.

what is the code behind your combo box? please paste it here.
is it actually ADDING another record, with the same info as an existing record?
what is your key field (the one that cannot be duplicated)?

 

[PharmaFun]

Combo Box: Row Source Type: Table/Query
Row Source: SELECT [Initial Report].[Number]
FROM [Initial Report];
Number is the key field that cannot be
duplicated.

If I try to edit it, I just get an error that duplicates are not allowed. There is no repititon of entries in the table, but I just want to be able to edit an existing entry and put it back in the table as the only entry with that number.

Thanks Ginger for any help you can provide!

D

 

[GingerR]

sorry for being unclear: what i wanted to see what the code that is in the AfterUpdate event of the combo box. from what you said, it seems as tho you choose an item from the combo box, and it navigates to that record? if not, please tell me where i'm wrong.

is your form's DATA ENTRY property set to YES?

 

[PharmaFun]

Hi Ginger...no apologies needed...your help is invaluable. Yes the Data entry property for the form is set to Yes. Here is the code associated with AfterChange on teh combobox:

Private Sub Number2_Change()
Initials = DLookup("[Initials]", "Initial Report", "Forms![Initial Patient Report]![Number2] =[Initial Report].[Number]&quot
End Sub

So it looks up the initials of the person based on the number chosen in the combo box. But what I want is to be able to edit those initials and put them back into the table as a single entry under the same number. If I do that, I get an error.

D

 

[GingerR]

ok um...trying to sort this out.
your form is set to DATA ENTRY. this means it always opens ready to type in a NEW record.
not quite understanding what you are doing with that dlookup statement. it seems to not be written properly. the 'where' portion of the statement should be referencing a field that is in your table, not the form. i think you may have it backwards:

DLookup("[Initials]", "Initial Report", "[Number] = " & Forms![Initial Patient Report]![Number2])

anyhow, honestly, i'm still at a loss to understand what you are doing.
if the form is set to DATA ENTRY, then it will open to add a new record. i'm wondering if you are actually changing your ID each time you select something diff in the combo box? i dunno without looking at it. if you want you can compact/zip and send it to me at rowe147@hotmail.com. just please put your tek-tips handle in the email so i know what post to look up.

 

[GingerR]

i looked at the db PharmaFun sent me--primary problem was that s/he needed to set the form's DATA ENTRY property to NO.