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

AnilBagga

Member
Local time
Today, 20:21
Joined
Apr 9, 2020
Messages
223
I have a form where the user enters data on the specifications of a bag and there is a query and a report to get the result of the exercise - the bag weight

It would be a good idea to have the result - the bag weight in the Form as soon as the user fills in all the data, like we get in Excel.

When I added the query field, the existing query data in all the records were updated!

Surprisingly now I am unable to add any new records in the form! If this was possible, the user would have to run the report or query to see this information!


Form name: frmUserInput
Final Query with bag weight : qryBagWeight2
Field name : BagWeight

The DB is enclosed.
 

Attachments

  • PP_Test.zip
    486.3 KB · Views: 167

June7

AWF VIP
Local time
Today, 06:51
Joined
Mar 9, 2014
Messages
5,423
Not surprising can't edit with that query included in form RecordSource.
Options:
1. DLookup() domain aggregate function: =DLookUp("BagWeight","qryBagWeight2","ErpFGCode='" & [ErpFGCode] & "'")
2. subform
3. listbox

Regardless, new record must be committed to table before BagWeight value will be available. So not sure how useful showing this value really is.

Record is committed when 1) close table/query/bound form or 2) move to another record or 3) run code to save.
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:51
Joined
May 7, 2009
Messages
19,169
you have wrong Join in your query.
you are joining the queies/table to SewingType field, which is not a Unique column.
you need to Join them in a Unique field, such as SewingCode.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 14:51
Joined
Feb 19, 2013
Messages
16,553
by joining tables together in a query, the query becomes unupdateable. There are exceptions, this isn't one of them, particularly as you are joining to another multi table query. Suggest remove the link and use Dlookup instead
 

June7

AWF VIP
Local time
Today, 06:51
Joined
Mar 9, 2014
Messages
5,423
As arnelgp says, some joins are just wrong.
In tblBagMasterWt, SewingCode is defined as PK yet you are saving SewingType into tblUserInput.
Similarly, in tblCutLength LengthID is designated as PK yet you are saving FoldType into tblUserInput.
And same situation with CustMasterTbl.
Linking should be on PK fields. And need to save PK as FK into related table.
 
Last edited:

AnilBagga

Member
Local time
Today, 20:21
Joined
Apr 9, 2020
Messages
223
you have wrong Join in your query.
you are joining the queies/table to SewingType field, which is not a Unique column.
you need to Join them in a Unique field, such as SewingCode.


The Query and the table are joined on a unique field - the ErpFGcode which is indexed and no duplicates
The other queries are joined differently for different reasons

For example in the query qryAddonBagBottom i had to select the weight from table based on the width range of one field and the type of Sewing type selected by a user. The SQL is as below

SELECT ErpFGCode, [WidthBag], "Width" &" "& WidthMin &" "&"to" &" "& WidthMax AS WidthRange, [tblUserInput].SewingTypeBottom, [WeightBottom]
FROM tblUserInput INNER JOIN tblBagMasterWt ON ([tblUserInput].SewingTypeBottom=tblBagMasterWt.SewingType) AND ([tblUserInput].WidthBag>=tblBagMasterWt.WidthMin) AND ([tblUserInput].WidthBag<=tblBagMasterWt.WidthMax);

In the Query qryBottomAddonLength, the user will select the sewing type among various options and there is duplication possible but the master table where the data is being fetched has no duplication in the joined field- Fold Type. It is not designated as a PK but can be done so. The data however is correct in the query. When we have a situation like this when we want a many to one query, what is the correct way?

Coming back to the original question, Dlookup is best way as informed. Can you help me on this

1. Is the query and table relationship correct in the form?
2. Can the dlookup be in a text box in the same form or I need a sub form?
3. June7 indicated a list box also. I have not used these before. How can i add this Dlookup with a list box?
 

June7

AWF VIP
Local time
Today, 06:51
Joined
Mar 9, 2014
Messages
5,423
Did you read post #5? Relationships need to be fixed. SewingType is not a unique value and cannot be PK.

Why not have SewingCode as an autonumber? There really is no reason to store the ST prefix.

Would not use DLookup with a listbox, it would go in a textbox on same form. However, until relationship for tblBagMasterWt is fixed, DLookup will not provide correct data. The report also won't be correct.

AddOnLengthBottom needs to be changed to a number (Double) field. Since these values are identical to AddonLengthTop, why have 2 fields?

Bind form to table.

Misspelling: TotClearWindowWldth
 
Last edited:

AnilBagga

Member
Local time
Today, 20:21
Joined
Apr 9, 2020
Messages
223
Did you read post #5? Relationships need to be fixed.

Why not have SewingCode as an autonumber? There really is no reason to store the ST prefix.

Would not use DLookup with a listbox, it would go in a textbox on same form.

Bind form to table.
1. The Form is bound to the table - tblUserInput.
2. There are 4 queries being used in this process.
In 2 queries we need multi selection in a relationship. The user inputs sewing type and the width of the bag. One sewing type is therefore linked to all the 9 width ranges with each combination having a different weight. We need the weight linked to the sewing type AND the range within which the width falls. I agree the PK could be an autonumber instead of STxxx but will it impact the result of the query? Is there are better way to handle this query?

In 2 queries we have an autonumber as PK. The relationship is on a text field which is not the PK. I see your point and will change the form to link the PK's. Surprisingly so far it is not impacting the results but would and the change is needed and the need understood.

Coming back to the Dlookup field, I have added the same and it works. Thank you!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:51
Joined
May 7, 2009
Messages
19,169
Dlookup will work for the description part since most record on your masterfile have same descript.
Dlookup will always return the first it will find.
 
Last edited:

AnilBagga

Member
Local time
Today, 20:21
Joined
Apr 9, 2020
Messages
223
Not surprising can't edit with that query included in form RecordSource.
Options:
1. DLookup() domain aggregate function: =DLookUp("BagWeight","qryBagWeight2","ErpFGCode='" & [ErpFGCode] & "'")
2. subform
3. listbox

Regardless, new record must be committed to table before BagWeight value will be available. So not sure how useful showing this value really is.

Record is committed when 1) close table/query/bound form or 2) move to another record or 3) run code to save.
Can you explain this Dlookup statement

We re looking for Bagweight in qrybagweight2 where the erpfgcode (of the record) is same as erpfgcode in the qry. Correct

The text '" & [ErpFGCode] & "' is not clear. A better understanding will help me learn
 

June7

AWF VIP
Local time
Today, 06:51
Joined
Mar 9, 2014
Messages
5,423
Yes, criteria is matching data in table with data on form. The value from form's record is concatenated into expression.

Did you review the revised db? I built 1 query that replaces your 6 queries and the query is editable and can be form's RecordSource.

I am looking at your VBA some more. Cancel = True does not work with AfterUpdate event, that is a BeforeUpdate event feature. And invoking it in a general module procedure won't work regardless.

Every module should have Option Explicit in header. Can set the VBE to do this by default: VBE menu > Tools > Options > check Require Variable Declaration. Will have to manually add to existing modules.
 
Last edited:

AnilBagga

Member
Local time
Today, 20:21
Joined
Apr 9, 2020
Messages
223
I have seen the DB but yet to go through it and understand it. Will do so today. I am sure I will have questions and would like your support.
 

June7

AWF VIP
Local time
Today, 06:51
Joined
Mar 9, 2014
Messages
5,423
I am attaching another version.
 

Attachments

  • PP_Test.accdb
    2.8 MB · Views: 152

AnilBagga

Member
Local time
Today, 20:21
Joined
Apr 9, 2020
Messages
223
I have gone thru the DB. So much less clutter to have only one qry. Thank you.

I have understood how to capture only the PK in the form and not the values and establish relationships with PK only

Just one observation - the user needs to select the range and the sewing type combination in the form when selecting sewing type top and bottom. There is a chance of error. The input to the user is all 5 variables - width, fold type top/bottom sewing type top / bottom. Can we eliminate this error possibility and ask user only to select the sewing type top and bottom without link to the width range?
 
Last edited:

June7

AWF VIP
Local time
Today, 06:51
Joined
Mar 9, 2014
Messages
5,423
Not sure I understand. I don't see anywhere for entry of width range, just WidthBag.

If you don't want SewingType Top and Bottom associated with a width range, why have them associated in tblBagMasterWt?

Instead of WidthMin and WidthMax in combobox RowSource, do you want WeightTop and WeightBottom instead?

Again, why have two fields with exactly same data: WeightTop is same as WeightBottom and AddOnLengthBottom is same as AddOnLengthTop.
 

AnilBagga

Member
Local time
Today, 20:21
Joined
Apr 9, 2020
Messages
223
Not sure I understand. I don't see anywhere for entry of width range, just WidthBag.

If you don't want SewingType Top and Bottom associated with a width range, why have them associated in tblBagMasterWt?

Instead of WidthMin and WidthMax in combobox RowSource, do you want WeightTop and WeightBottom instead?

Again, why have two fields with exactly same data: WeightTop is same as WeightBottom and AddOnLengthBottom is same as AddOnLengthTop.

Let me try to explain

1. The length value is dependent only on the fold type and is not dependent on with of the bag as can be seen in tblCutLength. The fold type top and bottom have different options. I have edited the table and ID 1 to 3 which relate only to the top fold type, the values of field Bottom weight are now 0 for these ID's and vice versa for the ID 4 to 7

2. The value of weight however is dependent on the width range of the bag and the sewing type. Therefore after a user enters the width in the form, and fold type top and bottom, the top and bottom weight should be selected from the table. At present in the cbo box has a drop down as below.

Example: User enters width value as 18 and top sewing type is Yarn Hemming. 18 falls under the range 17.01 to 21 and the value we need is 0.5gms. In the form when user selects the Top sewing type the cbo box appears as below. Therefore user has to make sure he selects the Yarn Hemming corresponding to the range 17.01 to 21. There exists a possibility of mistake. What we want is that the cbo box only shows Yarn Hemming as an option, without width range and the query selects the range based on the value 18, matches with the sewing type - Yarn Hemming and produces the result as per the table - ID 3, WeightTop value - 0.5gms

In my original DB I had configured the query on these lines with some help given in this forum. However the no of queries were too many then. Can you help and redesign the query for autoselection of the range when a sewing type only is used as an input

1596090823259.png
 

AnilBagga

Member
Local time
Today, 20:21
Joined
Apr 9, 2020
Messages
223
Let me try to explain

1. The length value is dependent only on the fold type and is not dependent on with of the bag as can be seen in tblCutLength. The fold type top and bottom have different options. I have edited the table and ID 1 to 3 which relate only to the top fold type, the values of field Bottom weight are now 0 for these ID's and vice versa for the ID 4 to 7

2. The value of weight however is dependent on the width range of the bag and the sewing type. Therefore after a user enters the width in the form, and fold type top and bottom, the top and bottom weight should be selected from the table. At present in the cbo box has a drop down as below.

Example: User enters width value as 18 and top sewing type is Yarn Hemming. 18 falls under the range 17.01 to 21 and the value we need is 0.5gms. In the form when user selects the Top sewing type the cbo box appears as below. Therefore user has to make sure he selects the Yarn Hemming corresponding to the range 17.01 to 21. There exists a possibility of mistake. What we want is that the cbo box only shows Yarn Hemming as an option, without width range and the query selects the range based on the value 18, matches with the sewing type - Yarn Hemming and produces the result as per the table - ID 3, WeightTop value - 0.5gms

In my original DB I had configured the query on these lines with some help given in this forum. However the no of queries were too many then. Can you help and redesign the query for autoselection of the range when a sewing type only is used as an input

View attachment 83866
Another thing

See DB enclosed. The Query and input table have 14 records. I dont see them when i open the form! I entered 2 values and data can be seen in table and Query but not in the form after closing and reopening it? The form source is the query and therefore all 14 records of the Query should display on the form!
 

Attachments

  • PP_Test_June7_30072020.zip
    482 KB · Views: 164

June7

AWF VIP
Local time
Today, 06:51
Joined
Mar 9, 2014
Messages
5,423
frmUserInput DataEntry property is set to yes so existing records will not display. The form only allows entry of new records.

Sounds like you need a cascading (dependent) combobox. Filter combobox list based on value entered in WidthBag. So user enters 18 and combobox list is limited to choices that have range that 18 fits. Then user can choose SewingType. Use combobox Conditional Formatting to disable it until a value is entered into WidthBag.

In Conditional Formatting click the Enable button and use rule:
Expression is: [WidthBag] Is Null

Then code like:
Code:
Private 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

Private Sub SewingTypeTop_GotFocus()
Me.SewingTypeTop.Dropdown
End Sub

Private Sub SewingTypeBottom_GotFocus()
Me.SewingTypeBottom.Dropdown
End Sub

Suggest you fix TabOrder of controls. Right click in Detail Section > TabOrder > AutoOrder
 
Last edited:

AnilBagga

Member
Local time
Today, 20:21
Joined
Apr 9, 2020
Messages
223
frmUserInput DataEntry property is set to yes so existing records will not display. The form only allows entry of new records.

Sounds like you need a cascading (dependent) combobox. Filter combobox list based on value entered in WidthBag. So user enters 18 and combobox list is limited to choices that have range that 18 fits. Then user can choose SewingType. Use combobox Conditional Formatting to disable it until a value is entered into WidthBag.

In Conditional Formatting click the Enable button and use rule:
Expression is: [WidthBag] Is Null

Then code like:
Code:
Private 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

Private Sub SewingTypeTop_GotFocus()
Me.SewingTypeTop.Dropdown
End Sub

Private Sub SewingTypeBottom_GotFocus()
Me.SewingTypeBottom.Dropdown
End Sub

Suggest you fix TabOrder of controls. Right click in Detail Section > TabOrder > AutoOrder

I did all this and it works very well. Thank you for your patience in bearing with a novice like me.

I changed data entry to No just so see if that causes an issue. Does not but the sewing type field is blank as it does not pull data from the table. If I enter the width again, then I get the option to change the selection. This is fine. The reason we want the old records at times in the form is to be able to edit the data in case an error is noticed

When i checked the data being calculated in the query I found the following issues

1. The width range to and bottom in datasheet view is incorrect
2. I inserted an expression - Extra which is nothing but a sum of 3 fields in the query. The value is incorrect. There is a reference to the table too in the expression as below but for some reason the value is incorrect. The weights in the query however are correct. It is referncing to these values which seems to be the problem

Extra: [ExtraAddOnWeight]+[tblBagMasterWt].[Weighttop]+[tblBagMasterWt].[weightbottom]

3. In the QBE view you have used tblUserInput.* in the first column. What is this?
In the data sheet of the query I see all fields of the table customer master but they are not there on the QBE grid. How? What can I do to remove some/all these fields?
 
Last edited:

AnilBagga

Member
Local time
Today, 20:21
Joined
Apr 9, 2020
Messages
223
DB enclosed
 

Attachments

  • PP_Test_June7_30072020.zip
    483.1 KB · Views: 156

Users who are viewing this thread

Top Bottom