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]

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.