Test Value in Temp Table Datasheet Does not Exist in Another Table (1 Viewer)

[SalisburyLad]

Hi All, I've been referring to this forum for a few years but never before needed to post but I cannot find a workable answer to a problem I have...

I have a database that collects information on building assets - asset number, description, location, age etc. We often have locations with similar assets so I am trying to create a form which takes data from a temporary table populated from the exiting data and queried by location - i.e. duplicate all assets in a given location. So far, so good. Now there may well be several assets in that new location so I have a form that displays a datasheet view of all the assets but leaves the asset number blank to allow a new unique asset number to be added. The aim is to run an append query to write these new assets to the existing table but.... I need to check, as each new asset number is entered, that it is a unique number (both in the original asset table and the temporary table I am editing). I do not want to get to the query only to find errors due to key violations! I've tried various bits of code using Dlookup() and Dcount() but as I am editing one record of many in the dataset I cannot seem to get either to look up the asset number in (queries referring to the two tables involved). Any advice gratefully received.

 

[stopher]

dcount seems a reasonable solution. Maybe you could paste the code you have tried here as well as the relevant query names and field names.
Chris

 

[SalisburyLad]

dcount seems a reasonable solution. Maybe you could paste the code you have tried here as well as the relevant query names and field names.
Chris

Private Sub NewAssetNum_BeforeUpdate(Cancel As Integer)
If DCount("ASSETNUM", "qryAllAssetNumbers", "ASSETNUM ='" & Me.NewAssetNum & "'") = 0 Then
MsgBox "There is no duplicate value in the Asset Table - " & TMPASSET
Else
MsgBox "A Duplicate Record Exists"
End If
End Sub

I know the Me.NewAssetNum doesn't work but I've left it in so you can see what I'm trying to do. Basically I need to take the value in the field I've just entered - (record #, field 1) and do the Dcount test. TIA

 

[Galaxiom]

The value property of a bound textbox is only updated when the record is saved.
You need the current text property.

Me.NewAssetNum.Text

 

[stopher]

As well as Galaxiom's excellent point:

Is ASSETNUM in your query a number or is it text? For a number use:

DCount("ASSETNUM", "qryAllAssetNumbers", "ASSETNUM =" & Me.NewAssetNum)

Chris

 

[SalisburyLad]

Guys, thank you so much. I knew I couldn't be too far off the mark but I just couldn't quite crack it. In the first place my temporary table had NewAssetNum as Text and it is a number in the original table - thanks Galaxiom your point about Me.FieldName.Text prompted me to check that and change it - once changed, Stopher's reply seems to have worked perfectly. Thanks again.