Bound form - insert or update based on key value?

**NeoPa** · Sep 7 '08, 10:31 PM

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

Code:

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 :

Code:

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** · Sep 8 '08, 01:34 PM

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** · Sep 8 '08, 02:14 PM

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** · Sep 8 '08, 03:09 PM

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** · Sep 8 '08, 03:21 PM

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** · Sep 8 '08, 08:23 PM

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** · Sep 8 '08, 08:37 PM

Originally posted by 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?

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** · Sep 9 '08, 05:39 PM

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.

Code:

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** · Sep 9 '08, 06:40 PM

Originally posted by cm5th

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!

**NeoPa** · Sep 9 '08, 07:45 PM

Before I leave you in what appear to be the fully-capable hands of cm5th, I will just explain that the Job Order Number control (you hadn't been very clear with control names at that point in the thread) that allows you to select the item you want (or add a new record) should NOT be bound and should be separate from the control where the current Job Order Number data is displayed on the form (which, of course, should be bound).

It is possible to do it with a single (bound) control, but even with clever coding it does cut down your options, and is actually not very intuitive for most users.

Feel free to go this route of course, but it wouldn't be my recommendation. A separate, unbound ComboBox control does the job for you quickly, fairly easily, and in a fashion which is immediately clear to anyone using it.

**cm5th** · Sep 9 '08, 07:55 PM

Originally posted by geolemon

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. ;-)

The ADD RECORD button opens up a new record for a bound form if the primary key is defined as AutoNumber and displays the empty (except for the primary key - JobOrderNumber) .

In your case, I'd open a recordset by selecting all records in tblJobOrder, then use ADDNEW, UPDATE to add a record, input the field values, and update the recordset. e.g. ...

Code:

Set rst1 = New ADODB.Recordset
      With rst1
        Set .ActiveConnection = CurrentProject.Connection
        .LockType = adLockOptimistic
        .CursorType = adOpenKeyset
        .Open "SELECT * FROM tblJobOrder"
        .AddNew
          .Fields("JobOrderNumber") = 'Predetermined Job Order Number"
          .Fields("Customer Name") = Customer Name
          .
          .
        .Update
      End With
      rst1.Close
      Set rst1 = Nothing

**geolemon** · Sep 9 '08, 08:08 PM

Originally posted by NeoPa

Before I leave you in what appear to be the fully-capable hands of cm5th, I will just explain that the Job Order Number control (you hadn't been very clear with control names at that point in the thread) that allows you to select the item you want (or add a new record) should NOT be bound and should be separate from the control where the current Job Order Number data is displayed on the form (which, of course, should be bound).

It is possible to do it with a single (bound) control, but even with clever coding it does cut down your options, and is actually not very intuitive for most users.

Feel free to go this route of course, but it wouldn't be my recommendation. A separate, unbound ComboBox control does the job for you quickly, fairly easily, and in a fashion which is immediately clear to anyone using it.

I absolutely *did* step into that pothole, in fact!

What I did first was try to bind the control... you can imagine how I broke it.

To work around the issue of needing that value inserted into my table (particularly as it's the key), what I did was add an invisible text box that was bound to the key column of the table. ...And I created a CustID text box to fix the Customer subform issue I was having before (I mention it as it's workaround is incorporated here too).

Then, I modified the VBA code that positions the other controls based on the value of the unbound control so that I have this logic:

Code:

IF control.value = "<ADD NEW>" then
     store my CustID value into a variable
     DoCmd.GoToRecord... (go to new record)
     set my CustID text box = CustID variable

ElseIf  rs.NoMatch
     set bound control = value of unbound control

Else
     position other form controls to currently selected record
End If

Any obvious flaws? And thanks for the good suggestions that got me here!

**cm5th** · Sep 9 '08, 11:36 PM

Originally posted by geolemon

To work around the issue of needing that value inserted into my table (particularly as it's the key), what I did was add an invisible text box that was bound to the key column of the table. ...And I created a CustID text box to fix the Customer subform issue I was having before (I mention it as it's workaround is incorporated here too).

Then, I modified the VBA code that positions the other controls based on the value of the unbound control so that I have this logic:

Code:

IF control.value = "<ADD NEW>" then
     store my CustID value into a variable
     DoCmd.GoToRecord... (go to new record)
     set my CustID text box = CustID variable

ElseIf  rs.NoMatch
     set bound control = value of unbound control

Else
     position other form controls to currently selected record
End If

Any obvious flaws? And thanks for the good suggestions that got me here!

It appears you are indeed using an unbound and visible combo box (as recommended by NeoPa) with the prompt added with the UNION clause. It's not clear to me how you accomplish your Else clause activity, but at this level I see no obvious problem. Pls let us know how you do it if it works.

I have a DB with a form that does something similar. However, I allow users to filter by combo boxes in the parent form and put my bound controls inside a subform. It has been working for adding new records, but I have some issues with helping the user supply a meaningful new value for a text field in my table that requires uniqueness (i.e. the true key). It's possible that I may need to adjust the record add design. So I'm interested in seeing how you do the record add.

**NeoPa** · Sep 9 '08, 11:43 PM

Originally posted by geolemon

I absolutely *did* step into that pothole, in fact!

What I did first was try to bind the control... you can imagine how I broke it.
...
Any obvious flaws? And thanks for the good suggestions that got me here!

I would only ask why it is so complicated.

You don't explain why you didn't go with the "almost done for you by the wizard" approach outlined above.

Bound form - insert or update based on key value?

Bound form - insert or update based on key value?

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment