• I am creating a new home page for this site, where the focus will be on directing people to the forums. If you would like to provide a testimonial, I would be most grateful. The thread where this is discussed can be found here: Seeking Testimonials Alternatively, just private message me.

Solved Using a Query and Table in form makes it uneditable (1 Viewer)

June7

AWF VIP
Local time
Today, 08:20
Joined
Mar 9, 2014
Messages
3,237
You may have read my previous post before final edit. I could not get reference to WidthBag to work in combobox RowSource property in design. Had to just set RowSource with VBA. The Requery lines are not needed. So set combobox RowSource in design to not include WidthBag criteria so values will show when form first opens.

Conditional Formatting rule has not set Enabled feature. Need to click "Enabled" button so Preview is grayed out and "Enabled" button has a blue border.

1. I don't understand this statement

2. Extra is only an alias name for weighttop field, there is no expression adding 3 fields - try:
Extra: [ExtraAddOnWeight]+[tblBagMasterWt].[Weighttop]+[tblBagMasterWt_1].[weightbottom]
I can see I missed this edit in my version of db.

3. tblUserInput.* uses wildcard so all table fields are pulled without having to explicitly reference each. My original query does not display additional fields. Switch to SQL view. There is a solo * character in front of FROM - delete it and comma in front of it.
 
Last edited:

AnilBagga

Member
Local time
Today, 21:50
Joined
Apr 9, 2020
Messages
57
You may have read my previous post before final edit. I could not get reference to WidthBag to work in combobox RowSource property in design. Had to just set RowSource with VBA. The Requery lines are not needed. So set combobox RowSource in design to not include WidthBag criteria so values will show when form first opens.

Conditional Formatting rule has not set Enabled feature. Need to click "Enabled" button so Preview is grayed out and "Enabled" button has a blue border.

1. I don't understand this statement

2. Extra is only an alias name for weighttop field, there is no expression adding 3 fields - try:
Extra: [ExtraAddOnWeight]+[tblBagMasterWt].[Weighttop]+[tblBagMasterWt_1].[weightbottom]
I can see I missed this edit in my version of db.

3. tblUserInput.* uses wildcard so all table fields are pulled without having to explicitly reference each. My original query does not display additional fields. Switch to SQL view. There is a solo * character in front of FROM - delete it and comma in front of it.
Good Morning

1. I have removed the Requery from VBA
2. I could not understand the conditional formatting correction. I applied the rule - see screenshot enclosed. Would appreciate some guidance. I checked online too. and saw videos too The values of sewing top and bottom are not visible for old entries and the sewing top and bottom are not greyed out
3. For different widths, the expressions in query are not giving the right value. See the screenshot enclosed.
4. The expression value Extra is resolved.
5. Wild card character removed in SQL view.
6. I had hidden the address fields etc of custmastertbl which were showing in the query. See screenshot. They are not referenced in SQL view too. I wonder why they show in the query
 

Attachments

June7

AWF VIP
Local time
Today, 08:20
Joined
Mar 9, 2014
Messages
3,237
Edit the rule, click on the itty bitty toggle button on the far right next to the font color button. The Preview should now show greyed and the button should have blue border.

What should width range be? They are calculated according to the SewingCode saved in tblUserInput. Edit records if wrong data entered. If you are using data I entered then I selected SewingCode before I understood association with WidthBag. I selected random records. So fix data. Maybe delete records and start over. These are just for testing, right?

If you want data to show in Sewing comboboxes when form opens, need to change RowSource in form Current event. When the form was set for DataEntry Yes, this was not an issue. Now need to change RowSource in multiple events.
Code:
Private Sub Form_Current()
If Not Me.NewRecord Then Me.WidthBag_AfterUpdate
End Sub

Sub WidthBag_AfterUpdate()
Me.SewingTypeTop.RowSource = "SELECT SewingCode, SewingType FROM tblBagMasterWt WHERE SewingCode<37 AND " & Me.WidthBag & " BETWEEN WidthMin AND WidthMax;"
Me.SewingTypeBottom.RowSource = "SELECT SewingCode, SewingType FROM tblBagMasterWt WHERE SewingCode>36 AND " & Me.WidthBag & " BETWEEN WidthMin AND WidthMax;"
End Sub
Fields are showing because of the solo * character. Not sure why query decided that * should apply to CustMasterTbl. The * and comma are still in the query. You removed the wrong one. I said to remove the one in front of FROM. Put tblUserInput.* back.
 
Last edited:

AnilBagga

Member
Local time
Today, 21:50
Joined
Apr 9, 2020
Messages
57
Edit the rule, click on the itty bitty toggle button on the far right next to the font color button. The Preview should now show greyed and the button should have blue border.

What should width range be? They are calculated according to the SewingCode saved in tblUserInput. Edit records if wrong data entered. If you are using data I entered then I selected SewingCode before I understood association with WidthBag. I selected random records. So fix data. Maybe delete records and start over. These are just for testing, right?

If you want data to show in Sewing comboboxes when form opens, need to change RowSource in form Current event. When the form was set for DataEntry Yes, this was not an issue. Now need to change RowSource in multiple events.
Code:
Private Sub Form_Current()
If Not Me.NewRecord Then Me.WidthBag_AfterUpdate
End Sub

Sub WidthBag_AfterUpdate()
Me.SewingTypeTop.RowSource = "SELECT SewingCode, SewingType FROM tblBagMasterWt WHERE SewingCode<37 AND " & Me.WidthBag & " BETWEEN WidthMin AND WidthMax;"
Me.SewingTypeBottom.RowSource = "SELECT SewingCode, SewingType FROM tblBagMasterWt WHERE SewingCode>36 AND " & Me.WidthBag & " BETWEEN WidthMin AND WidthMax;"
End Sub
Fields are showing because of the solo * character. Not sure why query decided that * should apply to CustMasterTbl. The * and comma are still in the query. You removed the wrong one. I said to remove the one in front of FROM. Put tblUserInput.* back.
1. Conditional Formatting - Done
2. Width range issue - Done
3. Current event VBA on the Form shows an error

Private Sub Form_Current()
If Not Me.NewRecord Then Me.WidthBag_AfterUpdate
End Sub
I tried Me.WidthBag_AfterUpdate() - still shows an error

1596180016893.png

4. I tried to follow your advise on the display of fields. Faced the following issue

As I understood this is to be used in SQL view of the query

Fields are showing because of the solo * character. Not sure why query decided that * should apply to CustMasterTbl. The * and comma are still in the query. You removed the wrong one. I said to remove the one in front of FROM. Put tblUserInput.* back.

There is only one FROM word in the SQL - there is no *, in front of FROM - see extract of SQL below

ight, *
FROM tblBagMasterWt AS tblBagMasterWt_1 INNER JOIN (tblBagMasterWt I

5. I did not see any place where CustMasterTbl.* is used in the query design or SQL view to justify all the CustMaster fields in datasheet view. Where is it?
 

Attachments

June7

AWF VIP
Local time
Today, 08:20
Joined
Mar 9, 2014
Messages
3,237
This is the solo * and comma in front of FROM, on previous line.

ight, *
FROM tblBagMasterWt

As shown in my code, need to remove Private from event declaration:

Sub WidthBag_AfterUpdate()

Otherwise, repeat the lines setting RowSource.
 

AnilBagga

Member
Local time
Today, 21:50
Joined
Apr 9, 2020
Messages
57
Perfect.

All issues resolved

Thank you much for bearing with me (y)🙏
Just when I thought I had understood everything, comes a surprise!

I wanted another element of a bag - Liner. I added 2 fields in the tblUserInput - LinerDesign which is a Numeric field to be linked to the data table LinerAddOn PK.

The moment I added this table in the qry and linked the fields, all previous data in the query vanishes - see DB enclosed. I did not even add a field in the QBE grid and I checked the join type - it is same as others :(

I don't have skills to check the SQL of the Query joining

Please help

PS - in the Query there are fields like below. Why are we using an alias?

1596205194238.png
 

Attachments

Last edited:

June7

AWF VIP
Local time
Today, 08:20
Joined
Mar 9, 2014
Messages
3,237
No data because query links are all INNER JOIN. This requires data on both sides of link for records to show. Change to RIGHT JOIN. Another issue I see is query design window is messed up. Tables are trapped above top edge and can't get them to pull down. So I copied SQL statement to a new query object and works much better. Delete old query and rename new one.

Using alias because same field pulled from two copies of table. Alias makes referencing field simpler.
 
Last edited:

AnilBagga

Member
Local time
Today, 21:50
Joined
Apr 9, 2020
Messages
57
No data because query links are all INNER JOIN. This requires data on both sides of link for records to show. Change to RIGHT JOIN. Another issue I see is query design window is messed up. Tables are trapped above top edge and can't get them to pull down. So I copied SQL statement to a new query object and works much better. Delete old query and rename new one.

Using alias because same field pulled from two copies of table. Alias makes referencing field simpler.
June7
Thanks. I created a new query,copied the old SQL, renamed it and changed all joins to include all records of tblUserinput and matching records of the rmasterdata. Looks good now. Tks
 
Last edited:

AnilBagga

Member
Local time
Today, 21:50
Joined
Apr 9, 2020
Messages
57
June7
Thanks. I created a new query,copied the old SQL, renamed it and changed all joins to include all records of tblUserinput and matching records of the rmasterdata. Looks good now. Tks
I feel very embarrassed:cry: in coming back to you

This is posing new challenges at every edit stage

After I edited the query, I added the fields in the form - Liner Type Liner GSM and weight. The cbo box does not capture the data. I double/triple checked the cbo boxes of Sewing code etc - it is the same as what I added. I even created an alias which was perhaps not needed. Again failure.

Why cant I get this right
 

Attachments

June7

AWF VIP
Local time
Today, 08:20
Joined
Mar 9, 2014
Messages
3,237
Alias for LinerType is not needed.

Liner combobox needs to be bound to LinerDesign field.
 

AnilBagga

Member
Local time
Today, 21:50
Joined
Apr 9, 2020
Messages
57
I feel very embarrassed:cry: in coming back to you

This is posing new challenges at every edit stage

After I edited the query, I added the fields in the form - Liner Type Liner GSM and weight. The cbo box does not capture the data. I double/triple checked the cbo boxes of Sewing code etc - it is the same as what I added. I even created an alias which was perhaps not needed. Again failure.

Why cant I get this right
The other thing I could not understand is how is the data of cbo being saved in tbluserinput?
The data source of the Form is the qry not to the tblUserinput
cbo TopFoldType is linked to tblCutLength and captures the lengthID
So how is the data of lengthID stored in the tbluseriput?

Similarly in cbo Country of the form, we select the valueCustCountry from CustMastertbl. This table is linked by custCode to tbl UserInput. So how is the CustCountry stored in tblUseriput?
 
Last edited:

June7

AWF VIP
Local time
Today, 08:20
Joined
Mar 9, 2014
Messages
3,237
Data passes to table through query.
Bind combobox to LinerDesign field. RowSource should not include tblUserInput. The RowSource you had before was correct.
 
Last edited:

AnilBagga

Member
Local time
Today, 21:50
Joined
Apr 9, 2020
Messages
57
Data passes to table through query.
Bind combobox to LinerDesign field. RowSource should not include tblUserInput. The RowSource you had before was correct.
Tks

See enclosed. I think this is what you advised.

With this logic, I still could not understand the below. I am sorry to bother you but I also want to learn at the same time

The cbo "TopFoldType" captures "lengthID" from the mastertbl and this is joined to "Topfoldtype" in tblUserInput" so this passage of data is understood. But by this logic the following is not clear

In cbo "Country" of the form, we select the value "CustCountry" from CustMastertbl. This table is linked by custCode to tblUserInput in the qry. So how is the CustCountry value get stored in tblUseriput? There is no Country field in QBE grid for a better understanding. Is it because of the cbobox name is Country? Does this mean that in such cases the cbo name should match the field in the tblUserIput for correct storage of data?
 

Attachments

June7

AWF VIP
Local time
Today, 08:20
Joined
Mar 9, 2014
Messages
3,237
I think I asked many posts ago why Country field is in tblUserInput or at least I meant to. It seems unnecessary. Country can be retrieved by linking with CustMasterTbl.

Country field is included in query by the * wildcard in tblUserInput.*. It is use of this wildcard that allows all fields of tblUserInput to be in query without having to explicitly list each. LinerDesign field is in the query twice. Once with the * wildcard and once because you have explicitly pulled into query. Remove the explicit reference to the field. Then change ControlSource to just LinerDesign instead of tblUserInput.LinerDesign.

I usually name controls different from fields, such as cboCountry, cboLiner, etc.
 
Last edited:

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom