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. cboJobOrderNumber,

1. set cboJobOrderNumber 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 cboJobOrderNumber.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. cboJobOrderNumber,

1. set cboJobOrderNumber 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 cboJobOrderNumber.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. tblBillingNumber) 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 tblBillingNumber).

2. Using a combo box like cboJobOrderNumber 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 cboJobOrderNumber.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 cboJobOrderNumber.BeforeUpdate (or AfterUpdate) event handler to watch cboJobOrderNumber.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. tblBillingNumber) 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 tblBillingNumber).

THAT's a very interesting way of handling it.
I wasn't seeing it as a "normalization" 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 cboJobOrderNumber 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 cboJobOrderNumber.BeforeUpdate (or AfterUpdate) event handler to watch cboJobOrderNumber.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]

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]

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

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]

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:

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]

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:

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]

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.

[NeoPa]

@cm5th

I hope you find something in here to help your situation. If not, feel free to post your own thread and, if you link in the thread address into a post in here (that won't be hijacking) I will make sure to check it out.

[geolemon]

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.

Sorry, I skipped some detail-
I *am* using your UNION suggestion - what a perfect way to get a selection like that into the list!

The logic is basically this, in that code:

• If the user selects <ADD NEW>, the form positions all the controls to the "new record" record - and essentially updates it. But since I'm updating the "new record" record, I'm inserting a record.
• "Else If" the user just starts typing a Job Order into the control, and it doesn't already exist in the list of existing Job Orders, then we're creating a new record as well using that part number (I may now see a flaw - I need some of the code in the "IF" section).
• "Else" the Job Order was picked from the list / matches one in the list, and the form controls position to that record.

My only additional complication (to I think answer your question, NeoPa) is that this is actually a subform - the table my subform is bound to has an enforced relationship to a Customer table, which the main form is bound to- so the user selects the Customer, and all Job Orders that correspond are made available in this subform.

...which is why I need to manipulate that Customer ID value - or I throw a referential integrity error. Small hoop to jump through... the Wizard wasn't a help. More like the Wizard of Oz! lol
[/quote]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.[/quote]
It actually seems to be working fine - the exception being the extra code I think I need to add... I obviously haven't even tested it completely enough to satisfy that IF condition.
So - I WILL get back to you, here!

And thanks to both of you for all the help so far!

[NeoPa]

No worries.

I'm a little confused on exactly where you are now, but you can worry about clarifying that later if it becomes necessary.