updating PARTIAL records bassed on query (1 Viewer)

desim

Registered User.
Local time
Today, 14:12
Joined
Dec 17, 2019
Messages
11
my existing table consist of address of new customers. the data is entered as work order tags are received. my portion of the database is to add to the existing data "AFTER" invoices are received and approved.
My portion is fairly easy, look up the address, check the box paid, add the date, and invoice number. well it sounds easy but there's about 100 address to look up, and only "Some" address within a certain street name is ready.
what I've been doing to make somewhat easier is I created a simple query on the street name, and I'm able to pick n choose the address based on the invoice.
I'm wanting to make it even easier, I'm wanting to 1), query based on the street name, 2), select "paid", based on the actual address in the invoice, and 3), have the date, and invoice fields completed after the "paid" invoice has been checked.
I created a continues form based on the query that brings them up, but because the form is populated by a query, I can't perform any updates. this is where I'm stuck. not sure where to go from here.
any suggestions would be greatly appreciated.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:12
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF!


I wonder if it's "safe" if you used code to do the update instead of directly on the form? To do that, you can use an UPDATE query.
 

desim

Registered User.
Local time
Today, 14:12
Joined
Dec 17, 2019
Messages
11
that does work, but because I have like (as an example) 20 address on "prosperity", but only 8 have been invoiced, the query would update "all" addresses on prosperity which I can't have.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:12
Joined
Oct 29, 2018
Messages
21,358
that does work, but because I have like (as an example) 20 address on "prosperity", but only 8 have been invoiced, the query would update "all" addresses on prosperity which I can't have.
Don't you have a primary key field in your table? If you have an ID field, you can use it to isolate the specific record you want to update.
 

desim

Registered User.
Local time
Today, 14:12
Joined
Dec 17, 2019
Messages
11
I do have an ID field. . .maybe you can make me think a different way.
so here's the story of my database. I'm an electrical provider, working on a new residential subdivision. when the service is established, one of our clerks adds the address to the database. now, the completed services are never in any order, and never on only one street, or even one subdivision for that matter. now, when the contract invoices me for the work they did, they supply me with the address they completed in a specified date range.
by the time I get it, there are many address. I've created a query that pulls up all the streets that are named "prosperity" but I have to pick and choose only those specific addresses that are in the invoice. I update those address with the invoice number, date, and a check box "paid" this way, I don't pay them twice for the same address.

any suggestions are greatly appreciated. sorry for the long story lol
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:12
Joined
Oct 29, 2018
Messages
21,358
I do have an ID field. . .maybe you can make me think a different way.
so here's the story of my database. I'm an electrical provider, working on a new residential subdivision. when the service is established, one of our clerks adds the address to the database. now, the completed services are never in any order, and never on only one street, or even one subdivision for that matter. now, when the contract invoices me for the work they did, they supply me with the address they completed in a specified date range.
by the time I get it, there are many address. I've created a query that pulls up all the streets that are named "prosperity" but I have to pick and choose only those specific addresses that are in the invoice. I update those address with the invoice number, date, and a check box "paid" this way, I don't pay them twice for the same address.

any suggestions are greatly appreciated. sorry for the long story lol
Hi. Actually, the way you explained that, then each address should be unique. Therefore, you should be able to update only the addresses you selected from the query result. For example, let's say your form is based on the following query:
Code:
SELECT * FROM TableName WHERE Address Like "*" & [Enter Address] & "*"
Now, when you open the form it asks you to enter an address and you enter "prosperity." The form might show 10 records that match the criteria. Now, if you want to update the third address on the list, you can use something like this in the Double-Click event of the Address field:
Code:
CurrentDb.Execute "UPDATE TableName SET FieldName=SomeValue WHERE Address = '" & Me.AddressControlName & "'"
Hope that helps...
 

desim

Registered User.
Local time
Today, 14:12
Joined
Dec 17, 2019
Messages
11
this is exactly where I'm stuck. I created the form, I have my field where I'm entering the name, but I don't know how to update my form to accept the query. I tried to create some sort of "afterupdate" to go to the address field and go to record, but obviously something is missing
 

desim

Registered User.
Local time
Today, 14:12
Joined
Dec 17, 2019
Messages
11
re reading your response, you said my form should be based on the query. . I actually did that, but I realized I was unable to update any field (even manually), because the form is based on a query. . I think that is where the start of my issues are.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:12
Joined
Oct 29, 2018
Messages
21,358
re reading your response, you said my form should be based on the query. . I actually did that, but I realized I was unable to update any field (even manually), because the form is based on a query. . I think that is where the start of my issues are.
Also, in my response(s), I have been saying since you can't update the data directly on the form based on the query, you might be able to use code to do it. And I just gave you a sample code to do that in my last post.
 

desim

Registered User.
Local time
Today, 14:12
Joined
Dec 17, 2019
Messages
11
oh. . I see what you did. . .you didn't update the field. . you updated the table. . ok. . I'll let you know how it goes. . thanks
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:12
Joined
Oct 29, 2018
Messages
21,358
oh. . I see what you did. . .you didn't update the field. . you updated the table. . ok. . I'll let you know how it goes. . thanks
Right. I thought you said you just wanted to update a date field or something, right?
 

desim

Registered User.
Local time
Today, 14:12
Joined
Dec 17, 2019
Messages
11
yes, three fields in total. question, I'm doing this code builder (obviously I know), I'm no expert at all, only done a handful of these in the past, I have not come across the CurrentDb.Execute "UPDATE TableName SET FieldName
this before. besides your "TableName. and "FieldName", I should use the rest as shown? I mean I know I have to use my own table name and field names. just never used the other
thanks again
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:12
Joined
Oct 29, 2018
Messages
21,358
yes, three fields in total. question, I'm doing this code builder (obviously I know), I'm no expert at all, only done a handful of these in the past, I have not come across the CurrentDb.Execute "UPDATE TableName SET FieldName
this before. besides your "TableName. and "FieldName", I should use the rest as shown? I mean I know I have to use my own table name and field names. just never used the other
thanks again
Hi. We can also go the easy way where you can try to build an UPDATE query using the query grid. So, create a new query and select the table you want to update, then change it to an UPDATE query. Select the fields you want to update and then in the Update To row, enter a form reference to the control on the form you want to use to update the field. For example:


Forms!FormName.ControlName


Then, in your code, you would simply run/open this new UPDATE query. You can use the Wizard to do that, but the code might look something like this:


DoCmd.OpenQuery "QueryName"


Hope that's easier/simpler...
 

desim

Registered User.
Local time
Today, 14:12
Joined
Dec 17, 2019
Messages
11
liking some additional help.
here's my code:


CurrentDb.Execute "Update Locations Set Invoice = 33333 WHERE Address = '" & Me.address & "'"



the 33333 was put there for testing. and it works, however, I'm needing "Invoice" to be set via another field "d-inv".


next, I need to do the above for two more fields. I tried the following:


"Update Locations Set Invoice = 33333, Set Paid = d-paid, Set Date = d-date WHERE. . . .



not working.
thanks
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:12
Joined
Oct 29, 2018
Messages
21,358
Hi. The syntax structure would be something like this:
Code:
UPDATE TableName SET Field1=Value1, Field2=Value2, Field3=Value3 WHERE FieldName=SomeValue
Hope that helps...
 

desim

Registered User.
Local time
Today, 14:12
Joined
Dec 17, 2019
Messages
11
it partially works. . I don't really want my Invoice to be set at 12242, I actually want it to retrieve from another field



CurrentDb.Execute "Update Locations Set Invoice=12242, Paid=yes WHERE Address = '" & Me.dAddress & "'"


for some reason, I'm unable to point to a field. I tried:
Set Invoice=[pinv]
Set Invoice=Me.pinv


neither of the two above options worked.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:12
Joined
Oct 29, 2018
Messages
21,358
Hi. Try it like this:
Code:
CurrentDb.Execute "Update Locations Set Invoice=" & Me.pinv & ", Paid=yes WHERE Address = '" & Me.dAddress & "'", dbFailOnError
The above assumes Invoice is a Number field. If it's Text, we'll need to add a text delimiter.
 

desim

Registered User.
Local time
Today, 14:12
Joined
Dec 17, 2019
Messages
11
ok, figured it out. .

CurrentDb.Execute "Update Locations Set Invoice='" & Me.dInv & "', Paid=yes, Paid_Date='" & Me.ddate & "' WHERE Address = '" & Me.dAddress & "'"



I really don't know how the '" & Me.dAddress & "' string works, but I figured it worked for the "Where" statement. . it must work elsewhere.


thanks a million for all your help. .
 

desim

Registered User.
Local time
Today, 14:12
Joined
Dec 17, 2019
Messages
11
that's too funny. . finished up my post, just after you posted your reply
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:12
Joined
Oct 29, 2018
Messages
21,358
that's too funny. . finished up my post, just after you posted your reply
Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.
 

Users who are viewing this thread

Top Bottom