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

[AnilBagga]

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;"

June7

This trail is old as I could not, for some reason, pursue this development and am now in the process to finalise this.

The DB is enclosed. I am facing one issue for which I need help. I need to develop a form for the table tblCustFabricConvRates. The queries are in place and working.

As you would see from the table structure, for each customer code there are 17 sets of rows of data and in these 17 rows, 2 sets of information - the fields GSMmin and GSMMax are repeated. From the data entry perspective, the form should be such that once a new customer is entered, the 17 rows of data are created in the form with pre-filled data of GSMMin and GSMMax and the balance 2 fields - UncoatedRate and CoatingRate blank, which the user would fill in.

In the conventional way of creating the forms, this is not possible!

 

[June7]

One approach is to have user initiate input of first record then code carries the GSMmin and GSMmax values forward to each of the following 16 records. Does user select the GSMmin and GSMmax values? Have them select in first record and code in textbox AfterUpdate event sets DefaultValue of each textbox and those values carry forward until user changes with another input.

Another is to 'batch' create a set of records. This requires VBA with loop structure and is a fairly common topic. Something like following example showing data for number, text, date fields:

Dim db As Database
Set db = CurrentDb
For x = 1 to 17
db.Execute "INSERT INTO tablename(fieldname1, fieldname2, fieldname3) VALUES(" & Me.tbxOne & ", '" & Me.tbxTwo & "', #" & Me.tbxThree & "#)"
Next

The real trick is figuring out what event to put code into. Could be a button click but this relies on user to push a button. To automate use form AfterUpdate event. So when user inputs the 'parent' record, code will run to input the 17 dependent records. Then code would present user with a form filtered for these 17 records to complete input.

 

[AnilBagga]

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

View attachment 83896

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?

Hi June7,

This form is working perfectly. I was too happy that i got the whole routine working and did not try to understand many details. This, when I want to now develop something else on similar lines, I tried to understand the logic and have got stuck at a point

See the query screen shot below. I saw the table, the values of the Sewing code are as per the width but in the cbo only the unique values sewing Type are populated, not all the rows with sewingcode>37. How is this filtered?

The column 20 is perhaps helping in selection of the exact sewing code based on the cbo selection and the width, but how is that happening?

What is the significance of the number 20 and why does the number 20 not get prefixed with the default Expr1:

 

[June7]

It doesn't prefix with Expr1 because the Show checkbox is not checked. This means this is just an expression in WHERE clause, not a field in SELECT. Switch to SQLView and see what I mean.

 

[AnilBagga]

It doesn't prefix with Expr1 because the Show checkbox is not checked. This means this is just an expression in WHERE clause, not a field in SELECT. Switch to SQLView and see what I mean.

Thanks June7. I dont know SQL but reading and understanding it in this case was not so difficult. Syntax maybe an issue in future. Will use this as base and develop. Thanks once again

 

[AnilBagga]

Hi June7,

I wanted to change the selection of the drown down of bottom stitching and modified the SQL as below. I don't get any syntax errors but the drop down rows of cbo are unchanged. What is wrong with this code

SELECT tblBagMasterWt.SewingCode, tblBagMasterWt.SewingType
FROM tblBagMasterWt
WHERE (((tblBagMasterWt.SewingCode) Between 19 And 27 Or (tblBagMasterWt.SewingCode) Between 37 And 54 Or (tblBagMasterWt.SewingCode) Between 64 And 81)
AND ((20) Between [WidthMin] And [WidthMax]));

 

[June7]

I tested the query in the old db. Only 5 records are retrieved. What output do you get?