Help building an Update record form.

[Perimeter]

There is an incredible amount of knowledge here and I have picked up so much from this site - this community is incredible so thanks in advance...

I have a table 'Main' with hundreds of records each with a unique field 'SerialNumber'
The records contain a dozen or so fields.

My goal is to create an "Update form" that a user will search for a record by entering a serial number and be presented with results.
Selecting the result they are then presented in the bottom area of the form, with the record with most fields displaying data but greyed out except for 3 or 4 fields like Status, Notes, Analysis that can have data entered into them.

Lastly a Submit button to commit the changes to the Main table. I shouldn't need any type of data validation, just simply enter what they entered into the Main table.

[davegri]

Seems pretty straightforward. Have you created the basic form with the display controls bound to the table? Have you created a combobox on the form to do the SerialNumber lookup?

[Welshgasman]

I would have a form with an unbound textbox for the search criteria in the header.
In the details section I would show the records.

I would use a bound form, so no need for a Submit button. Access commits the changes as you move off record

[Gicu]

Help for the search combo:
http://allenbrowne.com/ser-03.html
https://support.microsoft.com/en-us/...20click%20Next.
https://www.cimaware.com/expert-zone...ng-a-combo-box
The code created by the wizard (using bookmarks) can also be modified to be used in a textbox instead of the combo.

Cheers,

[Perimeter]

I do have a basic form with the display controls set to the Main table.
In that form I have an unbound text "Search Box" for inputting the desired serial number.
I have a list box below that display the results from the search which works great.
The rest of the form simply has the fields of the Main table.

Currently I am stuck on how to populate those fields when I click on the search result in the list box.
Additionally how to still display the data in all fields listed but "grey out" or prevent editing certain ones..

Thanks!

[Perimeter]

Additionally it would be nice if the fields didn't display any data until the searched record was selected in the list box.. if possible..

[davegri]

I have a table 'Main' with hundreds of records each with a unique field 'SerialNumber'

I don't understand the purpose of the listbox. If SerialNumber is unique, it can only display one record at a time.

[Perimeter]

I don't understand the purpose of the listbox. If SerialNumber is unique, it can only display one record at a time.

Looking at that now, yes - i guess i don't need the list box at all.

So i just need to enter the serial number is the search and have all the fields populate.. somehow.. maybe with the properties of each field->Data->Row Source pulling from the query used in the "SearchBox"
And then where can i set some of the fields to be greyed out or uneditable.


I know this is basic stuff, and i thank you all for your time, it's just gets insane surfing for individual aspects of what i'd like to do so I have landed on these forums.

[Perimeter]

Hmm not finding "Row Source" as an option for any of my "field" boxes.. only Control Source.. ..
Fun times..

[Gicu]

Have a look at the first link I've posted in post #4. Unless you have a very large number of serial numbers (in the tens or hundreds of thousands) you would be better if you replace the textbox with a search combo as it minimizes data entry errors due to the auto-complete feature. The combo would have the bound column the unique identifier (primary key) of your main table and that would be hidden, with a second visible column for the serial.
Open the form unbound or with some criteria to have the fields empty (for the form's record source use "SELECT * from tblMain WHERE True=False;") then in the AfterUpdate event of the combo once you selected the desired serial change the form's recordsource:

Me.Recordsource=SELECT * from tblMain WHERE ID_PK=" & Me.cboSearchCombo 'assumes the ID is numeric

As for the grey out fields you can disable them by choosing Enabled= False in the data tab of the controls property window.

Cheers,

[Gicu]

You don't need to touch the Control Source of each control, but manipulate the form's record source instead as just explained above...

Cheers,

[Welshgasman]

See how AB did it
http://allenbrowne.com/ser-62.html

[Gicu]

Hi Paul, your link would be a great learning tool, but isn't that a bit too much for the OP? I think the search would be limited to one field (serial number) so the regular unbound combo box search code would be enough.

Cheers,

[Welshgasman]

Hi Paul, your link would be a great learning tool, but isn't that a bit too much for the OP? I think the search would be limited to one field (serial number) so the regular unbound combo box search code would be enough.

Cheers,

Possibly, but I was just trying to show the logic, even if only for one field. You know what it is like, the O/P asks for one field and then comes back and asks 'how do I add a second field'?
Once you undertsand the logic, the rest should be easy?

[Perimeter]

Possibly, but I was just trying to show the logic, even if only for one field. You know what it is like, the O/P asks for one field and then comes back and asks 'how do I add a second field'?
Once you undertsand the logic, the rest should be easy?

That was exactly the case!

I did find I wanted to add more fields and the link was perfect in helping me understand the logic behind it.

Thanks everyone! I am just beginning my Access .. umm .. fun - and I am sure I'll be back with more noob questions!

Again thanks everyone for your time and patience!