Update Table With Form - Stuck :(

[mromberg]

Hi all,



I want to update a table using a form. The table is [DNU Audit Status].

I have built a form that has an unbound combo box with its row source looking for [SLS REP ID] in tbl [qry_Roster_XL_ActiveTerm].

Upon selecting the rep id from the above step there are two text boxes that will update the [Rep Name] with control source =[SLS_REP_ID_COMBO].[column](2) and [REP R12 Number] with control source =[SLS_REP_ID_COMBO].[column](1). So by picking the rep ID those two fields will display for the user. All that works fine.

I then have a number of fields on my form bound to [DNU Audit Status] that I would like the user to be able to input additional data.

Once all desired fields are populated I would like user to hit the [save and update] button to add or uppdate the records to my [DNU Audit Status] table.

If the [sales rep ID] is already in the [DNU Audit Status] table then i need to update the records. If not then add a new line.

Getting stuck on the update part as I feel that i need some code to get this to work. I've done similar activities but they have been bound and process was much easier.

Any and all help is appreciated!

[RuralGuy]

I would think you would want to execute a Domain Function (Dlookup, DCount) before deciding if you need an Append or Update query.

[mromberg]

Thanks Rural guy - that'll get me part way there. Maybe I'm completely missing something but next is how to get the table updated.

[RuralGuy]

So the first thing to do is determine if the [sales rep ID] is in the RecordSource of the bound table. That would involve a FindFirst in the RecordSetClone and either moving to that record or a new record depending on the results of the FindFirst. Do all of this in the AfterUpdate event of your ComboBox *before* you fill in the other controls from the results of the cbo selection.

[RuralGuy]

If it still does not want to cooperate, post back and we'll create the code.