Bound form - insert or update based on key value?

[geolemon]

This should be an easy one for anyone with basic forms experience (I'd hate for you guys to be bored!) :D

I have a simple form, bound to a single table.

I'd *like* the form to work this way:

• The form should open and be all blank.
• If the user selects the "Job Order Number" control and picks an existing Job Order Number, all the controls on the form will position to that job order.
  When the user manipulates the data in other form controls, the existing Job Order record is updated.
• If the user instead types in a new Job Order Number, and inputs/selects data into the other controls, this is inserted as a new Job Order record into the table.

On my 'form so far'...
I used the wizard to add one control with the "use the value of this control to select the value of other controls on my form" (that's not a quote - that's just the concept, I don't remember the exact wording... third option on the first Wizard page ;) ). That's the control for the key value in the table - "job order number".
...and that part works. I select a Job Order, the rest of the controls position to that record - and I can update it. Nice.

But, I can't "insert' using this form! I don't have a blank value to begin with - the form automatically positions to the first record. And, if I type a new value over the selected Job Order Number, it updates that record, changing the record to a new Job Order Number. Bad.

I'm guessing this is a simple, "forms 101" type of question - my apologies for that - at least I'm hoping it's a simple issue!

[NeoPa]

In your form's module you will find some code similar to :

Private Sub Combo6_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object
 
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[Symbol] = '" & Me![Combo6] & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Change this to :

Private Sub Combo6_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object
 
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[Symbol] = '" & Me![Combo6] & "'"
    If rs.NoMatch Then
        Call DoCmd.GoToRecord(Record:=acNewRec)
    Else
        Me.Bookmark = rs.Bookmark
    End If
End Sub

[geolemon]

Yes - I'm familiar with that first snippet of code, somewhat - i believe that code exists (or is triggered by, rather) in the "OnChange" event for the control on the key value that selects the record for the other controls to display?

Only thing I'm slightly confused by (and I'm sure I'll find out because of course I'm going to give this a try right now!), is whether that means the form will open up positioned to "all blanks"?

For some reason, I opened my laptop this morning, opened the database, opened the form... and all the controls showed blanks.
Halfway there?
Well, no - I input a "new" record, hit "--->" (a form "save record" button), and I got an error - something along the lines of "null values are not allowed for the primary key". I thought that was strange since I typed a new value into that combobox - but I see there's some code in what you posted that sounds like it more or less actually allows for a new record to be inserted?

Thanks for that - I'll let you know what happens!

[NeoPa]

It sould be fine.

The code handles an Null value specially, rather than trying to push it through the existing logic (as it was doing previously).

Let us know how you get on, but it should certainly go to the New record ;)

[geolemon]

Well, the behavior seemed correct when I first opened the form...
I also had to ensure that my JobOrders control was bound, lol (I must have really been in an 'only update' mood when I first created it!).

But anyway - something definitely isn't right.

When I first opened the form, I typed in a new Job Order Number, and sure enough - the form jumped to a blank entry (reflecting what's on the "new record" row on the table - fine!)
...but it ALSO blanked out my Job Order Number.

Interesting... so I retyped it.
And immediately got an error message "You cannot add or change a record because a related record is required in Customer".

...Well, fine - I simply hadn't gotten as far as that control, to pick a customer from my list! (there's a Customer table related to my Job Orders table, with RI enforced, of course)

AND, sadly - when I opened the Job Orders table to take a look, I see that it actually updated the Job Order Number (the key column) of the record that it initially was displaying on the form!

Is there a way to make the form behave the way I want it to?

[geolemon]

My initial thought is that I could solve *part* of the problem by positioning the form to that "new record" by default when the form opens...

...but that doesn't solve the problem of actually using the form controls to create the new record without setting off that Referential Integrity error (and even though I have the Row Source property set to limit the input choices to what is already in the Customer table, I'd rather not turn enforcement off).

Thoughts?

[cm5th]

Assuming you are using a combo-box to select existing job orders, e.g. cboJobOrderNumb er,

1. set cboJobOrderNumb er to FALSE

Now, when the user types in a new Job Order Number, you should get an NotInList event instead of what you are seeing.

2. In cboJobOrderNumb er.NotInList, write your code to create the new record.

Think this will work for you?

[geolemon]

Assuming you are using a combo-box to select existing job orders, e.g. cboJobOrderNumb er,

1. set cboJobOrderNumb er to FALSE

Now, when the user types in a new Job Order Number, you should get an NotInList event instead of what you are seeing.

2. In cboJobOrderNumb er.NotInList, write your code to create the new record.

Think this will work for you?

I'll try, but I'm guessing not, because when they type in a Job Number, there's still no value in Customer Name at that point in time - which I believe is what's causing the error to be thrown.

But provided it doesn't (does the NotInList happen similar to BeforeUpdate?), I could write code to create the new record at that point in time - the catch is, I don't have a value in Customer Name at that point in time (or any of the rest of the controls, for that matter), until the form user is able to fill them in.
...so effectively, I believe I can't write code to do the insert at that point in time.

I did find that if I set the form property 'Data Entry" to "yes", then it automatically positions (or seems to) to the "new record" record.
But that doesn't get me over the hurdle... same thing happens when I type in a new Job Order number.

I'm guessing (but haven't had a chance to experiment) that if I picked the customer from the drop-down first, that I wouldn't get this error -
...although I'm also half guessing that then I'll get an error message something to the order of "null value not allowed in key column" or something.

[cm5th]

I took a second look at your problem. LimitToList=NO gets complicated and it doesn't appear you really need the complication.

Let me backup and break your requirements down into two parts:

1. You need a form to add new Job Order records. Assuming that you defined JobOrderNumber as a primary key, all you need is a bound form, with a textbox bound to each field of your JobOrder table (tblJobOrder). To guarantee uniqueness for JobOrderNumber, defining its DataType as AutoNumber should do it. Then, if you include the VCR buttons for LAST(|<), BACK(<), FORWARD(>), LAST(>|), clicking LAST button, then the FORWARD button will position you at a new record with the JobOrderNumber already created for you.

By the way, in a fully normalized table, your primary key should be a long Integer, preferably with type AutoNumber to ensure uniqueness. You use it internal to your database. You can then have a second table (e.g. tblBillingNumbe r) that equate each of this unique number with an order number that is more suitable for your business (like containing alphanumeric characters that define Country, State/Region, City, etc). If you do this, the SQL statement you use for the RecordSource of your bound form should be a INNER JOIN of the both tables (tblJobOrder and tblBillingNumbe r).

2. Using a combo box like cboJobOrderNumb er simply makes it easier for you to find an existing job order instead of stepping through a long list.

If you use the Form Creation Wizard, there is an option to insert an ADD button. Its adds a record to your form. If you inserted an ADD button to your form, you can see the VBA code that adds a new blank record. In Access 2002 and Access 2003, its a DoCmd.ItemMenu. You can copy that statement into the appropriate event handler to create a new record that will be displayed in your bound form.

I take it that creation of a new JobOrderNumber needs to be a controlled process. You probably want it to be automatic (as when its DataType is AutoNumber) or program generated. In either case, you don't really want to let the user type in a number (so leave LimitToList=YES ), but you can add a prompt in the combo box drop-down list that displays something like <ADD NEW JOB ORDER>. You do this by adding a UNION clause to the SQL statement you use for cboJobOrderNumb er.RowSource, e.g.

SELECT tblJobOrder.JobOrderNumber, tblJobOrder.CustomerName AS CustomerName FROM tblJobOrder 
ORDER BY tblJobOrder.CustomerName
UNION SELECT "","<ADD NEW JOB ORDER>" AS  [CustomerName]
FROM tblJobOrder;

The prompt is displayed in the CustomerName column in the combo box drop-down list.

Now, what you need is to write VBA code in the cboJobOrderNumb er.BeforeUpdate (or AfterUpdate) event handler to watch cboJobOrderNumb er.col(1) for "<ADD NEW JOB ORDER>". If VBA sees the prompt, it executes the code to add a new blank record.

[geolemon]

By the way, in a fully normalized table, your primary key should be a long Integer, preferably with type AutoNumber to ensure uniqueness. You use it internal to your database. You can then have a second table (e.g. tblBillingNumbe r) that equate each of this unique number with an order number that is more suitable for your business (like containing alphanumeric characters that define Country, State/Region, City, etc). If you do this, the SQL statement you use for the RecordSource of your bound form should be a INNER JOIN of the both tables (tblJobOrder and tblBillingNumbe r).

THAT's a very interesting way of handling it.
I wasn't seeing it as a "normalizat ion" issue, but I can certainly appreciate it from a data integrity standpoint...
I did make a conscious decision to use the real "PO number" for simplicity's sake, and because that number is generated out of our Peachtree financial system. And because (sadly) I have myself invested in the entire process and not just the Access database, the root cause of a duplicate PO number will be upstream and should be tackled upstream (although no doubt this would help identify it - I'll have a direct duplicate key violation now, where I'd have to wrap code around it your way?).

That being said, I like the solution as it is fairly elegant and removes the pseudo-redundancy from my table. Ahhhh. That tension was part of the reason I didn't consider options outside of using it! So thanks.

2. Using a combo box like cboJobOrderNumb er simply makes it easier for you to find an existing job order instead of stepping through a long list.

If you use the Form Creation Wizard, there is an option to insert an ADD button. Its adds a record to your form. If you inserted an ADD button to your form, you can see the VBA code that adds a new blank record. In Access 2002 and Access 2003, its a DoCmd.ItemMenu. You can copy that statement into the appropriate event handler to create a new record that will be displayed in your bound form.

I've had this issue now with a couple forms... I've felt my workarounds to be a bit Rube Goldberg... code on the primary key control used to position to the selected record... modified to include "if not in the list, position to the new record".

Does the "ADD" button simply let the user press it and position the form to the blank record, essentially? That sounds nice actually. In fact, that sounds like a nice addition regardless of whether I've already coded it the other way. ;-)

I take it that creation of a new JobOrderNumber needs to be a controlled process. You probably want it to be automatic (as when its DataType is AutoNumber) or program generated.

It is - but sadly it's more of a controlled process in our Peachtree accounting software, where the job is really input, and financial implications recorded.
My Access database here is really a simpler tool to allow the parts manager to plan and build jobs against the job orders he's given to work against.
...in other words, the PO's and job orders exist in our systems prior to hitting this database.

...The prompt is displayed in the CustomerName column in the combo box drop-down list.

Now, what you need is to write VBA code in the cboJobOrderNumb er.BeforeUpdate (or AfterUpdate) event handler to watch cboJobOrderNumb er.col(1) for "<ADD NEW JOB ORDER>". If VBA sees the prompt, it executes the code to add a new blank record.

I like that better than the ADD button - thanks!