SQL Question (2 Viewers)

[alastair69]

Hello All,

I have a form that runs this section of code to generate a list of companies to call this works fine :

strSQL = "Update [JT Client List] set TempAccountManager = " & intAccountManager
strSQL = strSQL & ", TempAccountDate = #" & Format(CStr(datToday), "dd mmm yyyy") & "# "
strSQL = strSQL & " where ClientID in ( "
strSQL = strSQL & " SELECT TOP 50 [JT Client List].ClientID "
strSQL = strSQL & " FROM [JT Client List]"
strSQL = strSQL & " where (isnull([JT Client List].[TempAccountManager]) or [JT Client List].[TempAccountManager] = 0) "
strSQL = strSQL & " and (isnull([JT Client List].[AccountManager]) or [JT Client List].[AccountManager] = 0) "
strSQL = strSQL & " ORDER BY RandomNumber([ClientID]));"
dbs.Execute strSQL

What i need to do now is to setup away of setting a defualt number of records for example i need only 10 records, i now that if you alter the select 50 line to 10 this works but i need a way of doing this with out going into the cade each time.

The ideal solution would be a form that asks the user to input the value if one does not alread exist
Any body any ideas......

Alastair

 

[nateobot]

Dim iRecordCount as Integer
'get the value in the textbox on current form.
iRecordCount = CInt(nZ(Me.txtRecordCount, 0))
'set a default value
If iRecordCount = 0 Then
    iRecordCount = 50
End If
strSQL = "Update [JT Client List] set TempAccountManager = " & intAccountManager
strSQL = strSQL & ", TempAccountDate = #" & Format(CStr(datToday), "dd mmm yyyy") & "# "
strSQL = strSQL & " where ClientID in ( "
strSQL = strSQL & " SELECT TOP " & iRecordCount & " [JT Client List].ClientID "
strSQL = strSQL & " FROM [JT Client List]"
strSQL = strSQL & " where (isnull([JT Client List].[TempAccountManager]) or [JT Client List].[TempAccountManager] = 0) "
strSQL = strSQL & " and (isnull([JT Client List].[AccountManager]) or [JT Client List].[AccountManager] = 0) "
strSQL = strSQL & " ORDER BY RandomNumber([ClientID]));"
dbs.Execute strSQL

 

[alastair69]

Dim iRecordCount as Integer
'get the value in the textbox on current form.
iRecordCount = CInt(nZ(Me.txtRecordCount, 0))
'set a default value
If iRecordCount = 0 Then
    iRecordCount = 50
End If
strSQL = "Update [JT Client List] set TempAccountManager = " & intAccountManager
strSQL = strSQL & ", TempAccountDate = #" & Format(CStr(datToday), "dd mmm yyyy") & "# "
strSQL = strSQL & " where ClientID in ( "
strSQL = strSQL & " SELECT TOP " & iRecordCount & " [JT Client List].ClientID "
strSQL = strSQL & " FROM [JT Client List]"
strSQL = strSQL & " where (isnull([JT Client List].[TempAccountManager]) or [JT Client List].[TempAccountManager] = 0) "
strSQL = strSQL & " and (isnull([JT Client List].[AccountManager]) or [JT Client List].[AccountManager] = 0) "
strSQL = strSQL & " ORDER BY RandomNumber([ClientID]));"
dbs.Execute strSQL

Do i Need to create a text box called "Me.txtRecordCount", can this value be saved to a table in order to be able to set this once and then it will always be that value or do i need more coding.

Alastair

 

[nateobot]

Just creating the textbox should get you the desired result.

To save to a table you would have to add more code.

 

[alastair69]

Just creating the textbox should get you the desired result.

To save to a table you would have to add more code.

Thank you

Do you have any ideas of how to go about referencing in a save table result, the end result should be something like Alastair Lane = 10 Calls
John Smith = 15, i ready have a table with the user names. It would be logical for the results to be saved here by adding a column called "Calls", you can help i would be most greatful.

Alastair