Record Count

[CBRAY]

On my form I need to choose and compare the primary keys of two tables.

As I select each table and field a textbox COUNTSOURCE builds a SQL statement (basically a duplicate query). If I pass this statement to a como box all the records are listed, in my case 72 records. How could I actually pass this value to another text box called testcount. I am trying to do this by creating a recordset and then using recordcount but I end up with a value of 1.

Any ideas? here is the code:

Private Sub CMDCompare_Click()

Dim testcountval As DAO.Recordset

Set testcountval = CurrentDb.OpenRecordset(COUNTSOURCE.value)

testcount.value = testcountval.RecordCount

Set testcountval = Nothing

compareval.RowSource = COUNTSOURCE.value


'SELECT extGeneric.RECORD_IDENTIFIER FROM extGeneric, pdtInputObstacle WHERE (((extGeneric.RECORD_IDENTIFIER)=[pdtInputObstacle]![Obstacle_Id]));

End Sub

thanks

 

[Bat17]

at a guess

Set testcountval = CurrentDb.OpenRecordset(COUNTSOURCE.value)

testcount.movelast
testcount.value = testcountval.RecordCount


HTH

Peter

 

[CBRAY]

Thanks Peter

Of course

testcountval.movelast

It works a treat Cheers