Commandbutton - open form, hide fields in target form (1 Viewer)

Local time
Today, 23:09
Joined
Sep 23, 2021
Messages
32
Last question for now, I promise!

I am nearly, finally at the end of my to-do list for V2 of my database! Last thing on the list:

I am using a combobox to populate fields - it is working a treat! Only issue is that when I am scrolling back through records, the combobox is blank.

What I want to do is:

1. Click 'Edit record' button on menu form <- already working through 'on click' event
2. Form opens to last record <- already working through 'on click' event
3. Form DOES NOT display combobox row when in any record other than new <- cant figure this out :D

My issue seems to be that macros to hide fields will only work for the form that the button is currently in. I have tried 'on load' and 'current' macros too but cant get them to work consistently.

As always, help is both invaluable and deeply appreciated!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:09
Joined
May 7, 2009
Messages
19,169
which "fields" are being populated by the combobox and what column Number of
combobox does this field get populated?

if you can answer the question above, then you can add Code to the Current event
of the form to do the reverse, populate the combo from those fields.
 
Local time
Today, 23:09
Joined
Sep 23, 2021
Messages
32
Combobox is already working perfectly, I do want these fields to display the data as they are doing. All textboxes displaying data are locked to prevent any accidental edits which would throw the tables out of whack (yes I am duplicating data, shhhh I know).

The only change I want to make is:

On RecordADD, display combobox (I was going to hide the textboxes displaying combobox data but decided I dont mind them)
On RecordEDIT, hide combobox (set row height to 0)
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:09
Joined
May 7, 2009
Messages
19,169
what is the the row source of the combo?
I am duplicating data, shhhh I know
if the combo is about a person, you only need to save is the person's ID (not the first name and last name).
if the name change, it only change to the master table and reflected immediately to the other tables.
if you are saving the first_name and last_name, you'll have problem.
you change the record of one table and you need to change the master "names" table.
 
Local time
Today, 23:09
Joined
Sep 23, 2021
Messages
32
Row source for combobox is a custom query.

I appreciate the risk of running into issues, but it's a temporary best fit as I try to get workable database versions up and running. If my contract gets extended I will be able to do a lot more work on the database and with the data analysis officer on staff - right now I have to come at it from a excel-ish mindset at the data analyser only has experience with Excel.

The data admin person using the database can't make any changes to the fields being populated by the combobox, none of this data will be edited after initial entry in the Customer table. Best I could do!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 19:09
Joined
Feb 19, 2002
Messages
42,977
(yes I am duplicating data, shhhh I know).
And despite being aware that this is bad practice, you still want to do it? Your head will feel much better if you use best practices. The easy way to display the extra columns is with using a left join in the form's RecordSource. Just make sure the set the Locked property of the lookup data to Yes to prevent accidental updates.
 
Local time
Today, 23:09
Joined
Sep 23, 2021
Messages
32
Yes, because we have five years of existing data in that format (names etc in every row on every worksheet) and I don't have the luxury of time or authority to strip information out of the data to import to make it fit perfectly. If my contract gets extended (dependent on much more than just myself), I will worry about it, and ensuring the data analysis officer understands any formatting changes, then.
 

Mike Krailo

Well-known member
Local time
Today, 19:09
Joined
Mar 28, 2020
Messages
1,030
Don't worry about the five years of bad duplicated data. If you can design it correctly and get on the right track, then the old data can be imported later. Old data goes into a temporary table that is used to join with good data in your proper customer table. The final join will only work on the names without spelling or formatting errors. All other records will require some one else or you to parse through all the data and select the appropriate customer from the dropdown based on the new customer table. This gives those old records a customerID instead of current duplicate information that is probably mangled right now. Unfortunately someone will have to go though all those records and assign a customerID. But all new records will be properly created using the ID. Wasn't that the job you were contracted to do?
 
Local time
Today, 23:09
Joined
Sep 23, 2021
Messages
32
Don't worry about the five years of bad duplicated data. If you can design it correctly and get on the right track, then the old data can be imported later. Old data goes into a temporary table that is used to join with good data in your proper customer table. The final join will only work on the names without spelling or formatting errors. All other records will require some one else or you to parse through all the data and select the appropriate customer from the dropdown based on the new customer table. This gives those old records a customerID instead of current duplicate information that is probably mangled right now. Unfortunately someone will have to go though all those records and assign a customerID. But all new records will be properly created using the ID. Wasn't that the job you were contracted to do?
Hi Mike, thanks for the response. I'm just going to transform all the old data in Excel and import it (Xlookup and power query for the win bebe). I've learned a lot from the whole process and am studying database design in my own time so will be prepared for my next build.
 

Users who are viewing this thread

Top Bottom