Update only current record

[esskaykay]

I know this gets a bit convoluted but here it goes…

I’m having a problem with an SQL update query. I want to update the ParID field in tblPermits from frmAddress where a lookup field PIN has the correct data. Upon clicking the “Accept” button in frmAddress it should insert the correct ParID into tblPermits and opens the corresponding record in frmPermits.

If I code the frmAddress “Accept” button as following it updates the ParID field in tblPermits, however, it updates all records to the same data.

DoCmd.RunSQL "UPDATE tblPermits SET tblPermits.ParID = [tblParcels].[ParID]"

So I tried adding the where clause:
WHERE ((([tblParcels.ParID])=[forms]![frmAddress]![PIN]))"

but it returns “Invalid bracketing of [tblParcels.ParID].”

So I tried WHERE ((([tblParcels].[ParID])=[forms]![frmAddress]![PIN]))"

But that updated the ParID field in all records again to the same data.

Any suggestions would be greatly appreciated.

Thanks,
SKK

 

[jzwp22]

I think you need something like this:

DoCmd.RunSQL "UPDATE tblPermits SET tblPermits.ParID = " & me.PIN & " Where tblPermits.parID=" & me.ParID

This assumes that ParID is a numeric value in tblPermits. Is ParID the primary key of tblPermits? If so, that might cause problems.

 

[esskaykay]

ParID is not the primary key (Record_NO is) however, it is a text field. I'm assuming I need some other quotes.apastraphies?

Thanks,
SKK

 

[jzwp22]

Since ParID is text then you will need to add single apostrophes:

DoCmd.RunSQL "UPDATE tblPermits SET tblPermits.ParID = '" & me.PIN & "' Where tblPermits.parID='" & me.ParID & "'"

 

[esskaykay]

I'm sitll having a problem. I'd like to explain a bit more in detail what I'm attempting to do.

This is quite a detailed database with numerous things taking place. The primary concern is sidewalk construction inventory.

There is a tab control with numerous options of which one is PERMITS. This is where we track permits issued against properties where a permit has been issued for sidewalk construction. All permits are referenced to the tax key number. There are numerous Assessor’s tables linked to our sidewalk DB tracking ownership, legal description, etc. We must keyin a tax key number (aka ParID). In most instances the permit holder (i.e. owner) does not know his parcel number (i.e., ParID) but does know his address. However all our records reference to the parcel number. So I created a lookup form (frmAddress) where we key in the address and it returns the ParID. Here’s were the problem arises.

I have an ACCEPT button on frmAddress. When clicked, I want to populate the ParID field in tblPermits with the corresponding parcel number. There are other fields also required (i.e., OwnerName, Address, etc). I have the routine almost doing what I want.

Here’s the code:
Private Sub Update_PermitsAddr_Click()
DoCmd.RunSQL "UPDATE tblPermits SET tblPermits.ParID = [tblParcels].[ParID]"
DoCmd.Close
DoCmd.OpenForm "frmPermits"
DoCmd.GoToRecord , , acLast
End Sub

EXCEPT….
It updates all ParID’s to the selected record returned from the frmAddress. I want it to only update the current record. This is where I thought the DLast() would come in handy. I tried it but not being a programmer – failed.

I believe it’s in the RunSQL line. I assume I should include a WHERE clause -- but, not sure how. I tried:
…Where DLast([tblPermits.Record_NO])”

Am I anywhere near being successful or more likely, way off base. I hope this helps.

Thanks again,
SKK

 

[jzwp22]

You will definitely need a WHERE clause in your Update query otherwise you could conceivably update all records. You will also need to reference the form controls using the me.controlname syntax I provided earlier. What fields are in your address table and in your permit table? Can you link via the address field (i.e. use in the WHERE clause)? Theoretically, you would not even have to do the update. You can just use a DLookup() function to return the permit number given an address match (but the address must match exactly).

 

[esskaykay]

Thanks. Let me play with this a bit and hopefully I'll get somewhere. I'll keep you posted.

Thanks again,
SKK

 

[jzwp22]

Let us know how things work out.

 

[esskaykay]

You made me think more closely. I believe I may have looked at this wrong. Rather than an UPDATE I believe this should be an Append (Insert Into) query. Until I ACCEPT my frmAddress, there is nothing in tblPermits (although the record does exist). Is there a way to set my WHERE clause to update only the last record? I believe what I'm looking for is DLast() function. But, how do you code a WHERE clause to utilize DLast? Up until my frmAddress Accept button is clicked, the only data for the last record in tblPermits is the auto-number field Record_No.

SKK

 

[jzwp22]

I need to understand more clearly what tables you have and the fields involved; with that I think we can come to a quicker solution. Is the frmAddress bound to a table? Could you provide the table structure of the tables involved? If it is easier, could you zip and post a copy of your database with any sensitive data removed?

 

[esskaykay]

OKAY here we go. I attached a small sample of the database (the entire DB zipped is >3MB). I only included items needed to demo my problem relating to Permits.

1. Open Sidewalks DB
2. Click PERMITS tab
3. Click View/Edit Permits
4. There are three records in the tblPermits table. Note the ParID (PIN) in the upper right corner next to Legal/Desc PIN. When you scroll through the three records note the ParID’s or PIN’s changes
5. Close the View form
6. Click Add/New Permit - It prompts to Enter Notice ID.
7. For demo we are going to enter “N/A”
8. It prompts to Enter ParID. However we don’t know the ParID so we click “ParID Lookup”
9. Enter 1234 Westwood – it returns the record for such with a ParID = 272003180
10. Click ACCEPT button
11. It populated the frmPermits correctly – close the form
12. EXCEPT – now Click the Edit/View button and peruse thtough the four records. You will see it changed the ParID for all records in tblPermits to 272003180

I’m sure when you look at this you are going to be amazed at how inefficient and poorly the code is written. Please bear with me as I am no programmer.

Thanks,
Steve Kipping

 

[John Big Booty]

The SQL you are running under your Accept Button is updating ALL records with the selected permit, rather than only updating the current record. You will need to determine how you are going to isolate the current record and then update that alone.

 

[esskaykay]

I understand that. However, being the dummy I am, don't know how. Any suggestions would be greatly appreciated.

Thanks,
SKK

 

[jzwp22]

Going through your steps, you have not completed the permit form when you prompt for the ParID. You can just assign the value you accept back to the permit form (if it was open) but you closed it when going to the address form. So when you run the code to open the frmAddress do not close the permit form. Then in the Accept button you can use the following code to populate the ParID control on the permit form. You do not need a query.

Private Sub Update_PermitsAddr_Click()
    
Forms!frmPermits!PIN = Me.ParID
DoCmd.Close acForm, "frmAddress", acSaveNo
   
End Sub

I was also looking through your tables and I see several normalization issues. You have multiple owners listed in the tblParcels. If a parcel can have multiple owners then you have a one-to-many relationship which warrants the owners to be records in a separate table not fields in 1 table. I would also guess that an owner may own several parcels. To handle this, you need a junction table (see tblParcelOwners below)

tblParcels
-pkParcelID primary key, autonumber
-txtParID
other fields

tblPeople
-pkPeopleID primary key, autonumber
-txtFName
-txtLName
-fkPeopleTypeID foreign key to tblPeopleType

tblPeopleType (records for owner, contractor etc.)
-pkPeopleTypeID primary key, autonumber
-txtPeopleTypeDesc

tblParcelOwners
-pkParcelOwnersID primary key, autonumber
-fkParcelID foreign key to tblParcels
-fkPeopleID foreign key to tblPeople


To simplify things, I would put all people in 1 table and distinguish them by type.

Also in tblParcels you have multiple fields for the legal description. These should be in 1 field unless you need to distinguish each line. If so, then the legal description items should be records in a separate but related table. I see similar issues in tblGrades and tblLegals

tblParcelLegal
-pkParcelLegalID primary key, autonumber
-fkParcelID foreign key to tblParcels
-txtDescription
-fkLegalTypeID foreign key to tblLegalType

tblLegalType
-pkLegalTypeID primary key, autonumber
-txtLegalItemDesc

I also noticed that you have several assessed values associated with the parcel. Again these should be records in a related table. I also noticed that one of these assessed values is a total--this sounds like a calculated value which should not be stored in the table but just calculated on the fly when you need it in forms, reports and queries.

I noticed that you also have the concatenated name field in tblParcels. This is also considered a calculated value and should not be stored. It is redundant to store it since you have the other information used to build it. You can concatonate it using an expression in your forms, reports and queries.

 

[esskaykay]

Excellent,
Thank you very much. I think I got it. I'll keep you posted if things start to bomb.

Thanks again,
SKK

 

[jzwp22]

You're welcome!

 

[esskaykay]

OOPS...

I'm still having a bit of a problem. I'm receivng an error:
The data has changed
Another user edited the record and saved the changes before you...
Re-edit the record. -- OK

If I click OK it works fine, nut I'd rather not see the error

I tried coding DoCmd.SetWarnings False but no luck

Any other ideas?

Thanks,
SKK

 

[jzwp22]

I updated the database you attached earlier; the new version is attached. I could not duplicate the error you received. What were you doing at the time of the error? Is your DB multiuser? Do you have it split into front and back ends with the front end on each user's computer?

 

[esskaykay]

Excellent - this should do it. I've got a couple things I want to add to this but it looks great.

Thank you very much,
SKK

 

[jzwp22]

Glad it worked out for you.