How to change a record value from a split form dropdown?

csababenyi

New Member
Joined
Nov 17, 2020
Messages
11
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
  2. Web
Hi All.

Got a database that is used for input data through a form has several dropdown option selection. DB has about 20 columns.

One person has to change a value in a record in 1 cell, its an order status value ie: in ordered, shipped, received.

Once the record entered through the form what is best way to change that value? Can do it manually now but need to be dropdown with multiple selections.

Have a split form now that is used for filter the data in the database. Selected record shows on top, all the records on the bottom with the filter options. See image below.

1619164698960.png


Is there a way to have a dropdown in the split form shows the records?

Thanks in advance. Tried to decribe what I would like to accomplish best I can.
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,085
Office Version
  1. 365
Platform
  1. Windows
I haven't used split forms beyond playing with other people's db's when they have issues as there's too many issues with them. Did you try changing this status field in the 'main' form to a combo? I'm guessing the existing textbox is bound to the field where you want the value to go but you'll need a row source for it - something you won't have now. If there will only ever be a few status values, maybe just use a value list as the row source. When you change a selection in a bound combo, the chosen value gets placed into the bound field (its control source). All that assumes you can change the textbox to a combo (in design view, right click, choose Change To >).

AFAIK you can't do this in the bottom half. To do that you'd need what's called an emulated split form which I don't have a copy of. You could research that and probably find lots of examples.
 

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
885
If you set that control to a combo with the correct record data, then that is replicated in the datasheet portion of the split form?
Then you can change the data in either part of the split form, the single record part or the data sheet part.
 

csababenyi

New Member
Joined
Nov 17, 2020
Messages
11
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
  2. Web
Thank you for the advise. Tried a few things. Emulated Split Form is a bit over my head, maybe down the road.

1. Can change the field to a combo box in design view. 1 problem: Combo Box List character limit which I ran into at the data entry form.
2. Changing the field to combo box seems to disables the filter for that column, can type in keyword but that does not work for me.

Keep playing with it, but already made big progress, thanks again.

Do have another question not so unrelated to this.

Filling out the data entry form requires to input almost the same data may need to change 1 item but the rest of the fields are the same, sometimes 40-50 items.
Cant just change 1 field and hit save again ( created buttons ie new record, save record, print record, close form etc) it only saves the last entry.
Any way to save multiple times changing 1 thing? New record option basicaly clears the form and start over.

Thanks again, all your help is greatly appriciated.
 

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
885
An emulated split form is really just a subform for the same table/query and an extra two lines of code.?
A combo box is for selecting an entry that qualifies to a data item you want to store, not war and peace :)

I had a checkbox which was ticked on any form I used where I wanted to copy data to a new record.
This is my code on one of the forms and one of the controls.

Code:
Private Sub Date_ID_AfterUpdate()
    If Me.chkCopy Then
        Me![Date_ID].DefaultValue = """" & Me![Date_ID].Value & """"
    Else
        Me![Date_ID].DefaultValue = 0
    End If
End Sub

Here is a link also for another method Access/VBA Tutorials - Carry data over to new record

HTH
 

Watch MrExcel Video

Forum statistics

Threads
1,129,472
Messages
5,636,516
Members
416,922
Latest member
defectexpress

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top