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

[csababenyi]

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.

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.

 

[Micron]

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.

 

[jackd]

Further to micron's response, see this link re Emulated Split Form. I don't use split forms either.

 

[welshgasman]

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]

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]

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.

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

 

[csababenyi]

Hello again, thank you for all the help. Got sidetracked a bit, but got a work around may not be the most professional way to do it but works.

Ended up sticking with the split form, but minimized the top part so its not visible.
Changed that field to be a combo box, added the lsit items need to be in the drop down and used conditional formating for the color coding depends which value is selected.

Added a GUI form to select the Data Entry or Record Search. Works Great!

Thanks again, this forum is fantastic and learn a lot here!