check whether record already present before updating/appending?

[train2]

I have a situation where in most cases I will want to append a new record. however, it could be that an old inactive record (I have a flag to label it active or inactive), could just be reactivated. how can i check whether a record is already present before attempting to append?

I'm new to VB, but have done some SQL queries within VB to do the inserts and updates.
Any help appreciated.
train2

 

[KornGeek]

I have done something similar with VBA using the DCount function.

If Nz(DCount("[PrimaryKey]", "[tblMyTable]", "[PrimaryKey]='" & SearchValue & "'&quot,0) > 0 Then...

This uses the Nz to convert a null value to 0, and the syntax assumes that the PrimaryKey field is text. For a date field, you would replace the ' characters with #. For numeric data, you would remove those characters completely.

Hope this helps. Let me know if it's not clear.

 

[train2]

Thanks for response!
I was literally just trying with the SQL "select count" statement, but I'll try DCOUNT - seems a better use of VB!
I'll let you know.
train2

 

[train2]

Excellent - works perfectly. Exactly what I needed.

Took me a while to get the hang of the quotes etc. but it's working now.

I'm using this:
Existrec = Nz(DCount("[P_Id]", "[T_ClassPupil]", "[P_Id]= '" & cboPupil & "' AND [C_Id] = '" & cboToclass & "'&quot, 0)
Followed by an IF statement.

Thanks for help.

train2

 

[KornGeek]

Glad to see you got it working. Storing it in a variable before the If statement is actually the better way to do it. That way, if (when?) you need to debug your code, you can see the value returned by the function. In fact, it might even be a good idea to separate it even further into:

Existrec = DCount("[P_Id]", "[T_ClassPupil]", "[P_Id]= '" & cboPupil & "' AND [C_Id] = '" & cboToclas & "'&quot
Existrec = Nz(Existrec, 0)
...

I have used this structure when code doesn't behave the way I feel it should.

Good luck. I'm glad you got it working.