Query Result

[brianh]

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?

[omer555]

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.

[brianh]

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.

[brianh]

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.

[paulw]

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.

[brianh]

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.

[paulw]

How are you setting up cnndigi?

Easiest if you post the full code.

Paul.

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

[brianh]

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

[brianh]

Any suggestions?

[brianh]

Anything??????

[SysAdmin2]

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.

[brianh]

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

[SysAdmin2]

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

[paulw]

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

Cheers,

P.

[brianh]

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
____________________________________________________________

[brianh]

Anyone????

[LG]

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 & "'"

[brianh]

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..

[LG]

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