Re: Create a button to save/update record OR add new record

>. I was hoping that the
> code in Access could determine whether to update or add a new record based
> on
> the presence of either an existing or new primary key value, and not rely
> on
> the user searching and making the decision to Add or Save, but rather have
> the same button handle both tasks.

You can do the above, but the problem is that the form is bound to the
table. So, after you "find" a record using the comb box, the user edits the
record (and, hits your save button - but..as mentioned, that save process is
automatic in ms-access anyway). You are using the wrong concepts here for
ms-access. What works in FoxPro, or vb.net does NOT WORK for ms-access (the
trick here is modify your approach to how things work based on the tools you
use. As developers, if you don't modify your approach, then you will wind up
fighting the development process all the way).

The problem is now after the user edits, that record is STILL on the screen,
and STILL attached to the table. If the user starts editing the record in
hopes of adding a new record, you are fact editing the record that is still
displayed. (this is how bound forms work). I suppose you could put some code
in the save button to "empty" the record, but then again you are starting to
write too much code and fighting how the ms-access UI works (as mentioned,
if you fight the UI, then you start to write tons of code, and get no
benefits for this work).

One approach is to use the combo box not in list. Thus, if the user types in
a value/search in the combo that does not exist, then you could THEN prompt
the user to add the new entry. Remember, with a bound form, when the record
is displayed, it can be edited.

So, you need some caution if you are use the SAME form for searching, as any
record displayed will be edited if the user types into the form. It is going
to be VERY difficult to control editing of existing records if you allow a
user to start typing into a form that is bound to a table.

The combo box does have a "not in list" event that allows you to trap
entries typed in that are "not in the list".

As mentioned, a better choice might be to provide a search screen, and a
button to launch the main edit form after a record is found.

Further, you don't mention what kind of search. Is this a close name match,
or some part number (or id) that you search for?

If you are searching via some pk, then just provide a un-bound search form.

Place a text box on the form, and allow the user to type in a number, and
hit enter key (or a button if you wish).

Lets assume we are searching for invoice numbers. We will build a form with
a text box

Enter Customer Invoice number [ ]

This simple form we will turn off all the extra junk (record selectors,
navigation buttons, auto re-size etc.). The result is a plane jane form with
just the above text box.

The code in the after update event of the text box could be:

Private Sub txtInvoicePrompt_AfterUpdate()

Dim strSqlWhere As String

strSql = "InvoiceNumber = '" & Me!txtInvoicePrompt & "' "

If DCount("*", "tblCustInvoice", strSql) > 0 Then
' invoice exist...lets display the invoice form
DoCmd.OpenForm "frmCustInvoice", , , strSql
Else
If MsgBox("Invoice not found, add a new one?", _
vbQuestion + vbYesNo, "Add new") = vbYes Then
DoCmd.OpenForm "frmCustInvoice", , , , acFormAdd
End If

End If

End Sub

So, the above would accomplish our goal. Further, since we always launch the
invoice form to ONE record,then you can hide/disable the record navigation
buttons on the bottom of the form (which by the way allow you to move
around...and also ADD NEW records).


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@xxxxxxx
http://www.members.shaw.ca/AlbertKallal


.