Update a table value (1 Viewer)

Reshmi mohankumar

Registered User.
Local time
Today, 19:00
Joined
Dec 5, 2017
Messages
101
UPDATE tbl_Purchase_Details SET tbl_Purchase_Details.IS_Sold = True, tbl_Purchase_Details.bill_num = 123, tbl_Purchase_Details.sales_date = #5/30/2018#
WHERE (((tbl_Purchase_Details.BarcodeID)=[Forms]![frm_Estimation]![frm_estimation_details]![barcode]));


by this query i can update only cursor active field in subform (datasheet view), but i need all barcodes to be update of " [Forms]![frm_Estimation]![frm_estimation_details]![barcode])) " this subform.


please get me out from this....thank you
 

Minty

AWF VIP
Local time
Today, 13:30
Joined
Jul 26, 2013
Messages
10,355
What determines the subforms recordset? What is the parent ID that they are linked to ?

You should use that as criteria to return / update the same records.
 

Reshmi mohankumar

Registered User.
Local time
Today, 19:00
Joined
Dec 5, 2017
Messages
101
What determines the subforms recordset? What is the parent ID that they are linked to ?

You should use that as criteria to return / update the same records.


Barcode is parent ID , where active subform having multiple barcodes, those are should be updated to main table. here sub form having a duplicate data of main table.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:30
Joined
May 7, 2009
Messages
19,175
WHERE (((tbl_Purchase_Details.BarcodeID)=[Forms]![frm_Estimation]![frm_estimation_details].form![barcode]));
 

Reshmi mohankumar

Registered User.
Local time
Today, 19:00
Joined
Dec 5, 2017
Messages
101
WHERE (((tbl_Purchase_Details.BarcodeID)=[Forms]![frm_Estimation]![frm_estimation_details].form![barcode]));


Got input box
 

Attachments

  • rqy.JPG
    rqy.JPG
    70 KB · Views: 40

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:30
Joined
May 7, 2009
Messages
19,175
Put subst the name of the textbox for barcode not the name if the bound field. Also chk if you have"barcode" textbox.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:30
Joined
Feb 28, 2001
Messages
27,001
In this context, an Input Box says you either spelled or specified something incorrectly because it can't find the item named in the top of the box. It thinks that the item it named must be a parameter.

Verify the spelling of everything first. If the spelling is correct, then it is a problem with your use of "dot" and "bang" ( "." and "!" ) and in that case, there was a good thread on this forum recently. Search the forum for "dot and bang" and the article should come up pretty quickly.
 

Reshmi mohankumar

Registered User.
Local time
Today, 19:00
Joined
Dec 5, 2017
Messages
101
Put subst the name of the textbox for barcode not the name if the bound field. Also chk if you have"barcode" textbox.


All the thing is good i think!!?. but not perfect...
 

Attachments

  • rqy2.JPG
    rqy2.JPG
    57 KB · Views: 46

Reshmi mohankumar

Registered User.
Local time
Today, 19:00
Joined
Dec 5, 2017
Messages
101
Put subst the name of the textbox for barcode not the name if the bound field. Also chk if you have"barcode" textbox.


it was run, but updated only i item which is cursor is dropped that row only effected. but i have 2 items in subform all should be updated
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:30
Joined
May 7, 2009
Messages
19,175
do you run the Update query by Clicking on one of the button on
the form?
if yes then involve the subform's recordset on your update
by looping through it:

Code:
Private Sub yourButton_Click()
Dim rs As DAO.Recordset
Dim strUpdateQuery As String

strUpdateQuery = "Update tbl_Purchase_Details Set Is_Sold=True, " & _
	"bill_num=123, sales_date=#5/30/2018# Where " & _
	"BarcodeID=p1;"

Set rs = Me.RecordSetClone
With rs
	If Not (.BOF AND .EOF) Then .MoveFirst
	While Not .EOF
		With Currentdb.CreateQueryDefs("", strUpdateQuery)
			.Parameters(0)=rs![BarCode]
			.Execute
		End With
		.MoveNext
	Wend
	.Close
End With
Set rs=Nothing
End Sub
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:30
Joined
Feb 19, 2002
Messages
42,981
If the barcode value should be the same for ALL subform records, it doesn't belong in that table, it belongs in the parent table. Then you would only have to change it in one place.
 

Reshmi mohankumar

Registered User.
Local time
Today, 19:00
Joined
Dec 5, 2017
Messages
101
do you run the Update query by Clicking on one of the button on
the form?
if yes then involve the subform's recordset on your update
by looping through it:

Code:
Private Sub yourButton_Click()
Dim rs As DAO.Recordset
Dim strUpdateQuery As String

strUpdateQuery = "Update tbl_Purchase_Details Set Is_Sold=True, " & _
    "bill_num=123, sales_date=#5/30/2018# Where " & _
    "BarcodeID=p1;"

Set rs = Me.RecordSetClone
With rs
    If Not (.BOF AND .EOF) Then .MoveFirst
    While Not .EOF
        With Currentdb.CreateQueryDefs("", strUpdateQuery)
            .Parameters(0)=rs![BarCode]
            .Execute
        End With
        .MoveNext
    Wend
    .Close
End With
Set rs=Nothing
End Sub


I TRIED TO RUN THIS AND METHOD MEMBER DOES NOT EXISTS FOR Currentdb.CreateQueryDefs. ANY CORRECTIONS??
 

Reshmi mohankumar

Registered User.
Local time
Today, 19:00
Joined
Dec 5, 2017
Messages
101
If the barcode value should be the same for ALL subform records, it doesn't belong in that table, it belongs in the parent table. Then you would only have to change it in one place.




yes, but in update query where condition takes only one value. instead of one i need many..remain all the thing is good.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:30
Joined
Feb 19, 2002
Messages
42,981
You should review your schema design flaw. Why is the barcode in the subform table rather than in the main form table where it appears to belong?
 

Reshmi mohankumar

Registered User.
Local time
Today, 19:00
Joined
Dec 5, 2017
Messages
101
You should review your schema design flaw. Why is the barcode in the subform table rather than in the main form table where it appears to belong?


Design Flaw as in picture, Estimation_data is a table for only quotation for multiple items of tbl_Purchase_details and not effect to tbl_Purchase_Details items while i use Estimate command. If i use Sale command those selected products to be updated in required fields in tbl_Purchase_details.


I used sub form for quotation for multiple items .
 

Attachments

  • newqry.JPG
    newqry.JPG
    90.9 KB · Views: 39
  • rqy2.JPG
    rqy2.JPG
    57 KB · Views: 36

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:30
Joined
Feb 19, 2002
Messages
42,981
If Barcode is the primary key, it should not be changeable. The two estimate tables do not have barcodeID defined as their primary key. That might be correct since there might be more than one estimate but those tables need a primary key so use an autonumber and BarcodeID is the foreign key so connect the estimates to the actual.
 

Reshmi mohankumar

Registered User.
Local time
Today, 19:00
Joined
Dec 5, 2017
Messages
101
If Barcode is the primary key, it should not be changeable. The two estimate tables do not have barcodeID defined as their primary key. That might be correct since there might be more than one estimate but those tables need a primary key so use an autonumber and BarcodeID is the foreign key so connect the estimates to the actual.


Actually i am not going to change the barcode number, i am going to change the other columns details where the estimate sub form barcode values(i mean multiple items) is equal to parent table barcodes .
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:30
Joined
Feb 19, 2002
Messages
42,981
Storing the data in multiple tables is also incorrect. I assumed that the estimates might be different so I didn't mention that earlier. But, if you want all the columns to match, then the table design is incorrect. Do some reading on normalization to understand why. Then use a query that joins the two tables when you need data from both tables.
 

Reshmi mohankumar

Registered User.
Local time
Today, 19:00
Joined
Dec 5, 2017
Messages
101
Storing the data in multiple tables is also incorrect. I assumed that the estimates might be different so I didn't mention that earlier. But, if you want all the columns to match, then the table design is incorrect. Do some reading on normalization to understand why. Then use a query that joins the two tables when you need data from both tables.


i used update query it was success and updated parent table where the cursor was dropped that particular row only updating in parent table.
But actually i need all the rows should compare . how can i FOR loop after WHERE condition ? and
Just i want multiple barcode values from subform to update Query " WHERE value1,value2,value3,....:"
 

Minty

AWF VIP
Local time
Today, 13:30
Joined
Jul 26, 2013
Messages
10,355
You are missing the point being made here by Pat and others, the reason you are having to do a convoluted amount of programming and struggling to make it work is that your data design scheme is flawed.

If it was correct this would be simple, or more likely unnecessary.
 

Users who are viewing this thread

Top Bottom