Question on continuous form (1 Viewer)

GeorgieB

New member
Local time
Tomorrow, 01:35
Joined
Nov 28, 2020
Messages
12
Hi all,

TL,DR: :) I have a bound option box in a continuous form, and when I select any of the option buttons inside a record, I impact every row of the form

I'm new in the Forms development part of Access, and struggling a bit with the continuous forms.
I'm doing a project for a construction company on helping them with their estimation for various projects. They receive an Excel file with a lot of lines where they need to estimate how much that would cost(i.e. one line might be: "Installing steel pipes, category 1, size 20 cm"). They currently go to their excel files, search for steel pipe, than category 1, and then 20 cm, and see how much that would cost in terms of money.
Here's what I'm trying to accomplish:
I import the column of data from Excel which contains those lines(basically strings) in a table called tblMatch, and I'm trying to match each of them to a number of categories I have in different tables.(i.e one table has the main categories: pipes/electrical/concrete, second table has secondary categories: which in the example above is "category 1", and a column with a "parent category" which is the FK from the previous table. I'm going about 5 levels this way). I have additional columns in tblMatch which will hold these categories
I'm presenting a continuous form bound to tblMatch to the user where, for each record, I have a text field with the initial string, and them 5 combo boxes, one for each category. If I can find any of the tables' contents in each of the categories in the initial string, I will populate the respective combo box. This way the user will see the automated match, and be able to change to combination by using any of the combo boxes. This way tblMatch should contain the correct combinations at the end of this.
I want to add a way for the user to "lock" the combination by turning the Locked property of the combo boxes to True, and they query the db for the price. I've added a option group which is bound to a additional column in tbMatch, but when I select any of the option buttons inside, I impact every row of the form.
I've also looked at conditional formatting, but I get the same result.

Adding a pic of the form as well.
form view.PNG

Any idea how can I fix this?

Thanks,
George
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 23:35
Joined
Feb 19, 2013
Messages
16,553
I have a bound option box in a continuous form, and when I select any of the option buttons inside a record, I impact every row of the form
that behavior implies the option box is not bound to a field in the table. So double check that it is - you say the data comes from excel - so does excel have this field? or have you added it to the table?

With regards locking and continuous forms, conditional formatting does not allow for locking, only disabling

might be an idea if you can upload a copy of your db. remove any sensitive data and forms/tables/queries not relevant to your question. Then compact/repair to reduce size and then upload a zipped copy.
 

GeorgieB

New member
Local time
Tomorrow, 01:35
Joined
Nov 28, 2020
Messages
12
Adding the db. I have left a single form with a single option group, so it should be straight forward. Thanks for having a look.
 

Attachments

  • test1 - Copy.accdb
    3.3 MB · Views: 297

CJ_London

Super Moderator
Staff member
Local time
Today, 23:35
Joined
Feb 19, 2013
Messages
16,553
I've taken a look and your option group appears to work as expected i.e. a change in one record does not impact the other records

it's not clear to me how your category combo's are supposed to work - you are storing the text not the ID, your category2 table is storing both ID and text for category1 - you only need to store the ID, same for category3.

I suspect you are trying to use what is commonly called 'cascading combos' - for continuous forms you need use VBA to refresh the combo rowsource to display the filtered records on enter and again to display all records on exiting the combo - see this link

 

GeorgieB

New member
Local time
Tomorrow, 01:35
Joined
Nov 28, 2020
Messages
12
Hi,

I definitely wasn't clear on my issue, sorry about that.
The problem is that I have some VBA code on the option group buttons which basically disables/enables the combo boxes.

Code:
Private Sub opt_Corect_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
   
        Me.cboMainCat1.Enabled = False
        Me.cboMainCat2.Enabled = False
        Me.cboMainCat3.Enabled = False
        Me.cboMainCat4.Enabled = False


End Sub

When I use the option group in the current record, it disables the combo boxes in all the records, not just in the current one.

Thanks,
George

P.S. Those are cascading combos indeed. Not a pretty approach, but it works. I'll have a look at your link, thanks ;)
 

CJ_London

Super Moderator
Staff member
Local time
Today, 23:35
Joined
Feb 19, 2013
Messages
16,553
remove the code and use conditional formatting

also set the combos to enabled to undo whatever your code has been doing

conditional formatting rule would be

Expression Is ... [Frame49]=1

(or 2, depending on which way you want it to work)

and set the condition to disabled
 

GeorgieB

New member
Local time
Tomorrow, 01:35
Joined
Nov 28, 2020
Messages
12
remove the code and use conditional formatting

also set the combos to enabled to undo whatever your code has been doing

conditional formatting rule would be

Expression Is ... [Frame49]=1

(or 2, depending on which way you want it to work)

and set the condition to disabled
This is why I love the internet and forums like this. I've searched for this for the last 2 days.
@CJ_London thank you very much.
Have a great weekend ahead!
 

Users who are viewing this thread

Top Bottom