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

June7

AWF VIP
Local time
Today, 13:09
Joined
Mar 9, 2014
Messages
5,423
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
Tomorrow, 02:39
Joined
Apr 9, 2020
Messages
223
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

  • ConditionalFormatting.PNG
    ConditionalFormatting.PNG
    12.8 KB · Views: 123
  • WidthRange.PNG
    WidthRange.PNG
    28.6 KB · Views: 119
  • Hiddencolumns.PNG
    Hiddencolumns.PNG
    10.7 KB · Views: 133
  • PP_Test_June7_31072020.zip
    476.7 KB · Views: 130

June7

AWF VIP
Local time
Today, 13:09
Joined
Mar 9, 2014
Messages
5,423
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
Tomorrow, 02:39
Joined
Apr 9, 2020
Messages
223
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

  • PP_Test_31072020.zip
    475.6 KB · Views: 141

June7

AWF VIP
Local time
Today, 13:09
Joined
Mar 9, 2014
Messages
5,423
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
Tomorrow, 02:39
Joined
Apr 9, 2020
Messages
223
Perfect.

All issues resolved

Thank you much for bearing with me (y)🙏
 

AnilBagga

Member
Local time
Tomorrow, 02:39
Joined
Apr 9, 2020
Messages
223
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

  • PP_Test_31072020.zip
    478.5 KB · Views: 139
Last edited:

June7

AWF VIP
Local time
Today, 13:09
Joined
Mar 9, 2014
Messages
5,423
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
Tomorrow, 02:39
Joined
Apr 9, 2020
Messages
223
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
Tomorrow, 02:39
Joined
Apr 9, 2020
Messages
223
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

  • PP_Test_31072020.zip
    493.7 KB · Views: 130

June7

AWF VIP
Local time
Today, 13:09
Joined
Mar 9, 2014
Messages
5,423
Alias for LinerType is not needed.

Liner combobox needs to be bound to LinerDesign field.
 

AnilBagga

Member
Local time
Tomorrow, 02:39
Joined
Apr 9, 2020
Messages
223
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:

AnilBagga

Member
Local time
Tomorrow, 02:39
Joined
Apr 9, 2020
Messages
223
Alias for LinerType is not needed.

Liner combobox needs to be bound to LinerDesign field.
See enclosed- failed again. I have done these cbo multiple times. This query of course is far more complicated.
 

Attachments

  • PP_Test_01082020.zip
    479.6 KB · Views: 139
Last edited:

June7

AWF VIP
Local time
Today, 13:09
Joined
Mar 9, 2014
Messages
5,423
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
Tomorrow, 02:39
Joined
Apr 9, 2020
Messages
223
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

  • PP_Test_01082020.zip
    475.5 KB · Views: 130

June7

AWF VIP
Local time
Today, 13:09
Joined
Mar 9, 2014
Messages
5,423
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:

AnilBagga

Member
Local time
Tomorrow, 02:39
Joined
Apr 9, 2020
Messages
223
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.

Hi June7

I am back to trouble you again

The selection process of selecting data based on a range had to be duplicated in another form and I created the tables and form. However the selection statement does not work

The related tables and forms are

tblCustFabricConvRates this is the source from where we need to access data in the form
tblCustRFQ - the table linked to the form
frmCustRFQ

In the form we need to refer to 2 reference values - UncoatedGSM and EndCustomer (which stores the CustomerCode in the table). We want to fetch the value of StdRateID of the record from the tblCustFabricConvRates where the EndCustomer of the form = CustID in tblCustFabricConvRates and where the UncoatedGSM value in the form is BETWEEN the GSMMin and GSMMax in tblCustFabricConvRates

A similar situation as configured by you in the other form in the DB - frmPricingItemMaster.
"
I tried to add the following VBA code in afterupdate of ERPFGCode without the word "Private" in the code - as below. However since the uncoated GSM is updated after selecting the ErpFGCode, I added the code in the After update of "Qty"

Sub Qty_AfterUpdate()

Me.UncoatedGSMID.RowSource = "SELECT Stdrateid,Uncoatedrate FROM tblcustfabricconvrates WHERE " & Me.EndCustomer & " = CustID AND " & Me.UncoatedGSM & " BETWEEN GSMmin AND GSMMax;"

End Sub

The value would be populated in this record is ID 5 (GSM range 40.01 to 50) and Custid = 1104 (same as in the tblCustRFQ)
 

June7

AWF VIP
Local time
Today, 13:09
Joined
Mar 9, 2014
Messages
5,423
None of those objects are in the posted database. What table are GSMmin and GSMmax in?
 

AnilBagga

Member
Local time
Tomorrow, 02:39
Joined
Apr 9, 2020
Messages
223
None of those objects are in the posted database. What table are GSMmin and GSMmax in?

I perhaps forgot to attach the latest DB. Enclosed now.
 

Attachments

  • PP_Test-10Aug.zip
    499.8 KB · Views: 128

June7

AWF VIP
Local time
Today, 13:09
Joined
Mar 9, 2014
Messages
5,423
Would only remove Private if want to call from another procedure. Guessing you plan to do that because not it seeing it now.

Need apostrophe delimiters because CustCode, CustID, EndCustomer are all text fields.

Me.UncoatedGSMID.RowSource = "SELECT Stdrateid, Uncoatedrate FROM tblCustFabricConvRates WHERE CustID='" & Me.EndCustomer & "' AND " & Me.UncoatedGSM & " BETWEEN GSMMin AND GSMMax;"
 

Users who are viewing this thread

Top Bottom