Moving to an Unbound Record Selection Form -- Refresh Issues, Changed Data Errors

[thadius856]

The main form on my Access 2013 database right now is a basically a record selection form. The intent is for it to load blank until the user selects a record from a combo box, then populate fully and enable applicable buttons. I've been 90% successful in converting it to an unbound form.

When the form was bound to a table, I used the default combo box navigation macro that Access generates (basically DoCmd.SearchForRecord) and loaded the form empty in Form_Load (DoCmd.GoToRecord , , acNewRecord). Data was displayed in disabled text boxes (for values straight from the table) and single row listboxes with queries having TOP 1 (for values that required a query). Life was good and easy, until one of my users figured out how to re-enable the text box and edit my table directly. I need my users to only be able to update records using pre-defined actions which I've made buttons for, so this is a no-go for me.

I replaced the text boxes with labels -- they're blanked on Form_Load and then populated using AfterUpdate on my record selection combo box (by setting .Caption, of course). I built queries for my listboxes as well -- the RowSource is blanked on Form_Load and then set using the combo box. I love that I can now change field colors based on their values and give more intuitive data than just a blank value!

But I'm now running into two issues. They're probably trivially easy to fix, but my Google skills and/or vocabulary seem to be leaving me just short.

1st: Each of my buttons makes changes to 1-2 tables using DAO. When the form was bound, I was able to refresh the record just before exiting the button's sub (Me.Refresh), but of course now that doesn't work. I tried swapping that out for a Requery on the combo box that sets all the captions/rowsources, but that doesn't seem to do anything. So the user clicks the button, gets a beep that it completed, and the record doesn't appear to update... understandably confusing them and often causing them to repeat the action again. Smart users will click my old refresh button (DoCmd.RunCommand acCmdRefresh), which does nothing... again, I assume that method only works on bound forms.

2nd: Users then realize they can refresh the record by selecting another record (or the same record again) from the combo box. Sometimes they'll get an error... other times they won't. I haven't been able to discern why it only happens sometimes. "This record has been changed by another user since you started editing it" My understanding is this should only be possible when there's bound controls, but I don't seem to have any left!

[orange]

I don't mean to be negative, but your post seems to be saying "this is HOW I did something and it isn't working as I want".
We really need to hear more about WHAT you are trying to do in plain English.

If it really is to find records based on some search criteria, I suggest you review this by Allen Browne. If I've guessed incorrectly, please tell us more.

[June7]

If user can figure out how to enable textbox then they can figure out how to get to tables directly. Is this a split db? Perhaps you should be deploying an executable accde frontend. But then I've never resorted to accde and they do have their own issues.

For the first item, exactly what is the issue - refreshing the data displayed on the form or requerying the listbox?

For the second, I've never used UNBOUND forms for data entry/edit/display so not sure why this error occurs but somewhere in your process a record is placed in edit mode then there is some other attempt to do an edit on the same record.

[thadius856]

I don't mean to be negative, but your post seems to be saying "this is HOW I did something and it isn't working as I want".

I assumed you'd need that info.

We really need to hear more about WHAT you are trying to do in plain English.

Sure thing. I was given oversight of a project which has the sole purpose of track items. I need to know where every single item is at any given moment and be able to generate specific reports monthly/quarterly/annually.

The last guy tried to track it all with spreadsheets and paper sign-in/-out logs, with predictable failure. Notably, the spreadsheet got to 8 tables, each ~30 columns wide and ~500 rows deep. The ~30 authorized issuers were getting overwhelmed trying to keep track of themselves and the 50 users, and the record keeping fell apart because they couldn't track down which users were making the (plentiful) data entry errors. And of course the reports took day-to-weeks to generate, still full of those errors.

My fix is a transition to a db where users can only perform pre-set actions. That's what you see in the picture below. I've sanitized some of the info.



Here's the basic workflow I envisioned:

1. User walks up an issuer and requests an item.
2. Issuer selects the item from the combo box to select its record.
3. Issuer clicks Check Item Out, which opens a popup (not shown) with a combo list all the users with current training dates and some instructions.
4. Issuer clicks a Complete Check Out button, which fires off two ADO calls; one to update the item's status, one to create a new transaction.

The info isn't in bound text boxes. It's individual labels controls which are updated by the combo's AfterUpdate. So in this case, "Checked Out? No" doesn't change to "Yes" until you re-select the item from the combo.

I tried forcing a .Requery on the combo box which sets the label .Caption, but that doesn't seem to trigger the AfterUpdate.

I also tried making that AfterUpdate a Public Sub and calling it just before closing the popup, but I get "Object doesn't support this property or method."

If it really is to find records based on some search criteria, I suggest you review this by Allen Browne. If I've guessed incorrectly, please tell us more.

It's not. But this is an interesting read. Thanks!

[thadius856]

If user can figure out how to enable textbox then they can figure out how to get to tables directly. Is this a split db? Perhaps you should be deploying an executable accde frontend. But then I've never resorted to accde and they do have their own issues.

It is not a split db yet, but it will be. Yep, likely an accde frontend.

I'm still working out the bugs. I'll want a real Ops Test with some of my... wiliest... users before deploying fully.

For the first item, exactly what is the issue - refreshing the data displayed on the form or requerying the listbox?

Data on the form. More details above.

I figured out the listbox portion last night over a few beers.

For the second, I've never used UNBOUND forms for data entry/edit/display so not sure why this error occurs but somewhere in your process a record is placed in edit mode then there is some other attempt to do an edit on the same record.

Turns out it was the listboxes not being refreshed. If you selected an item in the Transactions listbox, then made a transaction, then switched to any other record, you'd get the error. I have no idea why, but it was squashed as a by-product of updating the listboxes.

[June7]

If you are going to split db and deploy accde, then why UNBOUND form?

[thadius856]

If you are going to split db and deploy accde, then why UNBOUND form?

Unbound form on recommendation from a mentor.
Deploying as ACCDE as a best-practice from Googling.
Are they not necessary in conjunction?

Unfortunately, I'm about 8 hours into converting it to an unbound form. As a result, a lot of my code in other tables/modules has had to change to accommodate it. I'll bite the bullet and switch back if I have to switch back, but if I could just get the labels to update I could wipe my hands and call it a day.

[thadius856]

I went ahead and just added a few lines to update the labels from the buttons after the DAO calls. It's a slight dupe of code, but I suppose I'll live.

Next time I guess I'll decide on ACCDE or UNBOUND forms. Seems like I made it harder on myself than I needed to. :\

[June7]

I have never used ACCDE. But I don't have to worry about my users messing with frontend design or trying to work with tables directly.

I have never used UNBOUND forms for manipulating data to/from tables. I have enough code without that headache.