I'm concatenanting to fields to get a Primary ID. How do I update? 1

[b31luv]

I'm taking two fields to make a primary key. The first part of the field will be the same for every record.

I.E. BSP

The second part of the field is the part that is different.

I.E. 1, 2, 3, 4, or 5.

In the event the first part of the field has to be changed to, let say, DSP I would like all of the records that have BSP as the beginning part of it's ID to be changed to DSP.

To illustrate:

BSP-1 Change to DSP-1
BSP-2 Change to DSP-2
BSP-3 Chagne to DSP-3
BSP-4 Change to DSP-4

When I view the main table BSP has changed for all records to DSP.

I'm using

DoCmd.RunSQL "Update [tbEquipment] Set [tbEquipment].EquipmentID = '" & Me.Site & "' Where [tbEquipment].AreaID = '" & Me.Area & "'"

This code only updates the current record and as you can see there is no concatenation of any kind. When I try to concatenate '" & Me.SiteID & "' & " - " & '" & Me.TagNumber & "' I get a type mismatch.

Thanks in Advance for any type of assistance.

 

[Remou]

Hi
How about:

DoCmd.RunSQL "Update [tbEquipment] Set [tbEquipment].EquipmentID = '" & Me.Site & "' Mid([tbEquipment].EquipmentID,4) Where [tbEquipment].AreaID = '" & Me.Area & "'"

You metioned that you had two fields, if your key is AreaID, Number and you have a table Area related to tbEquipment, perhaps you could take advantage of cascade update?

 

[b31luv]

I'm going to try your suggestion later on today.

I have the relationship set for cascading update. But I think maybe I have too much happening.

Fundamentally, I have a table for sites, then one for areas, then one for equipment, then equipment specifications. Each is linked as follows:

To begin I have [tbSiteName].SiteNameID. For this I grab bits and pieces from [tbSteName].SiteName to provide me with a Primary Key that is not the name of the site. I did this hoping to simplify things.

I.E. Big Bens Water Treatment Plant is BBWTP

[tbSiteName].SiteNameID links to the [tbArea].SiteNameID. Thinking I was simplifing things again I combined [tbAreaID].SiteNameID with an abbreviation that will be chosen by the client for the Area which provides me with the primary key for that Area [tbArea].AreaID.

I.E. Big Bens Water Treatment Plant, Aeration Basin No. 1 is BBWTP-AB1

[tbArea].AreaID links to [tbEquipment].AreaID using BBWTP-AB1. Here, the tag number [tbEquipment].TagNumber for that piece of equipment is concatenated with [tbEquipment].AreaID to provide me with the [tbEquipment].EquipmentID. The reason for this is that the equipment ID for one site may be the same as for another site if I had chosen to use the tag number as the primary key.

The problem lies here. There will always be at least 3 pieces of equipment in each area. So [tbEquipment].EquipmentID provides me with a primary key to link the equipment specification with that piece of equipment. Now let's say that I have inputted the information for all of the equipment and then I was advised to change the name of the area from BBWTP-AB1 to BBWTP-AB112. Due the the cascading update the [tbEquipment].AreaID updates as it should. However, [tbEquipment].EquipmentID still reflects the previous AreaID.

I.E.
Tag Number = PSH-112
Area ID = BBTWP-AB1
Equipment ID = BBTWP-AB1-PSH-112

Change in Area ID.
Tag Number = PSH-112
Area ID = BBTWP-AB112
Equipment ID needs to reflect BBTWP-AB112-PSH-112, however, it reflects BBTWP-AB1-PSH-112.

Thanks for your assistance. I'll respond and let you know what happens.

 

[Remou]

Hi
I think what you are looking at is:
[tt]tblSite
SiteID ) Key

tblArea
Area_SiteID )Combined for key
AreaID )

tblEquipment
Equip_SiteID )Combined for key
Equip_AreaID )
EquipmentID )[/tt]

Relate tblArea to tblSite on SiteID->Area_SiteID
Relate tblEquipment to tblArea on both Area_SiteID->Equip_SiteID and AreaID->Equip_AreaID
Enforce Referential Integrity and Allow Cascade Update.

You have a unique key for each. In the case of tblEquipment the key consists of three fields. Now, if I change AreaID in tblArea, the change is reflected in tblEquipment.

I hope this is what you are aiming for.

 

[b31luv]

Remou,

I never thanked you for your help previously. I posted another thread based on this information. Maybe you can assist me.

(New Thread)
I've tried looking through the threads to find something to lead me in the right direction. I have made some changes to my database based on information I received in this form. Previously I was concatenating cells to get my primary key. Now I'm leaving the cells or fields as they are and making two attributes equal my primary key. I use a button control to open a form. When I setup the button, I was only able to use one stLinkCriteria.

Private Sub OpenEquipment_Click()
On Error GoTo Err_OpenEquipment_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmEquipment"
    
    Select Case fraContract
    Case 1
        strLinkCriteria = "[SiteNameID]=" & "'" & Me![SiteNameID] & "'"
    Case 2
        strLinkCriteria = "[AreaCode]=" & "'" & Me![AreaCode] & "'"
    Case Else
    End Select

    stLinkCriteria = "[SiteNameID]=" & "'" & Me![SiteNameID] & "'"
    stLinkCriteria = stLinkCriteria 'And "[AreaCode]=" & "'" & Me![AreaCode] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_OpenEquipment_Click:
    Exit Sub

Err_OpenEquipment_Click:
    MsgBox Err.Description
    Resume Exit_OpenEquipment_Click
    
End Sub

This is some code I found and revised to what I thought was correct.

I removed this portion on purpose because I keep getting this type mismatch error:

'And "[AreaCode]=" & "'" & Me![AreaCode] & "'"

Any help would be deeply appreciated.

 

[PHV]

stLinkCriteria = stLinkCriteria 'And "[AreaCode]=" & "'" & Me![AreaCode] & "'"
Syntax error !
Either try this:
stLinkCriteria = stLinkCriteria & " And [AreaCode]='" & Me![AreaCode] & "'"
Or this:
stLinkCriteria = stLinkCriteria & " And [AreaCode]=" & Me![AreaCode]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244