Query Result

If I want to query a DB and prompt the user (via a msgbox) for a date. And then have one field fill its content according to the criteria (the date) how would I do it?

get the date from the user into a variable of date type and then do this

suppose your table name is emp
sqlstring = insert into emp(date) values (blah,blah,datevariable,blah,blah)

rset.opendatabase(sqlstring)
where rset is an object of type recordset.

OK so I would declare a couple of string, such as:

dim strquery as strring
dim sql as string

Then I would get an input box such as

strquery = InputBox("Please enter a date")

Then I owuld place the contents of that Inputbox into a sql statement:


sql = "select * from tbldigidoc where tbldigidoc.date like 'strdate' "

Then I would get the contents of that sql statement (This part os where I am clueless and the part of getting the contents of the input box:

rs.open sql, conn

rs is a recordset and conn is a new adodb connection.

Then I would loop:

do until rs.eof
lstquery.additem rs("name_of_doc")
rs.movenext
loop

rs.close
set rs = nothing
end sub

Now lstquery is a list box, name_of_doc is a field name in my DB. I want the appropriate name_of_doc contenst according to the date (which is another field name) that is input into the input box. Am I making sense? If not please tell me where I need to explain more. Thanks for any help.

Ooops! I am ment in the sql statement to say like strquery but that was is not my problem with this one. Just thouhght that I would clarify that. Any suggestions would be appreciated.

Nearly, but no cookie.

sql = "select * from tbldigidoc where tbldigidoc.date like '" & strdate & "'"

Otherwise you will look for the literal value "strdate" and no records will be returned.

I am pretty sure that whatever db you are using 'strdate' will not do it. In Access it would be "... #" & strdate & "#"

Might be easier to use a date picker control.

P.

It is crashing on this line:

rsdigi.Open sql, cnndigi

and saying object required. If I place my mouse over sql at run time it has the sql statement in there. When I place it over cnndigi (Which is an adodb connection) it saya empty.

How are you setting up cnndigi?

Easiest if you post the full code.

Paul.

P.S. use the "[code ] [/code ]" flags.

Here is the code:


___________________________________________________________
Private Sub dbConnect()
Dim cnndigi As New ADODB.Connection
Dim cmddigi As New ADODB.Command
Dim rsdigi As New ADODB.Recordset

Set cnndigi = New ADODB.Connection
cnndigi.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=G:\DigiSend\digi\db\dbDigiSend.mdb"

Set rsdigi = New ADODB.Recordset
rsdigi.CursorType = adOpenKeyset
rsdigi.LockType = adLockOptimistic
rsdigi.Open "tblDigiDoc", cnndigi, , , adCmdTable

cmddigi.Name = "command"
cmddigi.ActiveConnection = cnndigi

End Sub
____________________________________________________________

I then call this with this code:

____________________________________________________________

Private Sub Query()
Call dbConnect
Dim sql As String
Dim cl_id As String
Dim typeofdoc As String

strdate = InputBox("Please neter the date that you would like to search for")

If strdate = "" Then
MsgBox "Please enter a date", vbOKOnly
End If

sql = "select * from tbldigidoc where tbldigidoc.date like #" & strdate & "#"

rsdigi.Open sql, cnndigi

Do Until rsdigi.EOF
lstquery.AddItem rsdigi("Name_of_Doc")
rsdigi.MoveNext
Loop

rsdigi.Close
Set rsdigi = Nothing

End Sub
____________________________________________________________

Thanks

Any suggestions?

Anything??????

Your recordset, adodb command and connection are out of scope. You dim them inside dbConnect and when you exit dbConnect, they are lost (out of scope). I would dim the connection public and dim your recordset and command in the function you are using them in.

Would you mind giving me an example. I dont have much experience with this, sorry. I appreciate the help.

Option Explicit

Public adoCONN as ADODB.Connection

______________________________________________________

Private Sub dbConnect()

set adoCONN = New ADODB.Connection

adoCONN.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=G:\DigiSend\digi\db\dbDigiSend.mdb"

End Sub

______________________________________________________


Private Sub Query()

Dim strDate as Date

Dim strSQL as String
Dim adoCmd as ADODB.Command
Dim adoRS as ADODB.Recordset

dbConnect

strDate = InputBox("Please neter the date that you would like to search for")

If strDate = "" Then
MsgBox "Please enter a date", vbOKOnly
End If

strSQL = "SELECT * " & _
"FROM tbldigidoc " & _
"WHERE tbldigidoc.date like #" & strDate & "#"

With adoCMD
.ActiveConnection = adoCONN
.CommandType = adCmdText
.CommandText = strSQL

Set adoRS= .Execute()
End With

Do Until adoRS.EOF
lstquery.AddItem adoRS("Name_of_Doc")
adoRS.MoveNext
Loop

rsADO.Close

Set adoRS= Nothing
Set adoCMD= Nothing

End Sub


HOPE THIS HELPS

Sorry brianh for not replying (I went home) but SysAdmin is absolutely correct.

Cheers,

P.

I would like to know do it with a string object in the input box. I get an error message that states object variable not set, it crashes on the line

Set rsdigiID = .Execute()

Here is my code:
___________________________________________________________
Private Sub CLID_Query()
Dim strSql As String
Dim strCLID As String
Dim rsdigiID As New ADODB.Recordset
Dim condigiID As New ADODB.Command

lstquery.Clear
dbConnect

strCLID = InputBox("Please enter the Client ID that you would like to search for")

If strCLID = "" Then
MsgBox "Please enter a Client ID", vbOKOnly
End If

strCLID = "select * from tbldigidoc where tbldigidoc.Cl_ID & like & strCLID"

With condigiID
.ActiveConnection = cnndigi
.CommandType = adCmdText
.CommandText = strCLID

Set rsdigiID = .Execute()
End With

Do Until rsdigiID.EOF
lstquery.AddItem rsdigiID("Name_of_Document")
rsdigiID.MoveNext
Loop

rsdigiID.Close
Set rsdigiID = Nothing
Set condigiID = Nothing

End Sub
____________________________________________________________

Anyone????

This line is totally wrong:
strCLID = "select * from tbldigidoc where tbldigidoc.Cl_ID & like & strCLID"

It should be:

strCLID = "select * from tbldigidoc where tbldigidoc.Cl_ID like '" & strCLID & "'"

It owrked and thank you very much. I have a question what is the purpose of the '. I am confused on why this is necessary..

The string in a where clause should be inclosed in single quotes.