Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Query Data Needed In Message Box 2

Status
Not open for further replies.

LincolnMarkV

Programmer
Dec 22, 2003
72
US
Happy New Year!!!

I have built a scheduling database that is used for workshops. Everytime a new workshop is scheduled, the BeforeUpdate event checks for conflicts in the existing schedule. It does this by using a Dcount versus a query that pulls from the existing schedule. If there is a conflict, a message box is displayed. I want to be able to list the class information in the message box so they know which workshop is conflicting. The query used to check lists all of the information I need.

How can I capture the data from the query that is being run so I can use the fields in the message box?

Thanks,
David
 
user a recordset to open the query and return data from it into a string, then put the string to the message box.

Randall Vollen
Merrill Lynch
 
Use the DLookUp function instead of DCount.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
PHV,

How would I use a DLookUp to verify? Right now, if the Dcount > 0, then I know there is a conflict. This seems to work.

I have done what hwkranger is suggesting before with no problem. For some reason, I cannot seem to get it to work. Can you give me a quick example?

Thanks,
David
 
It will be the identical syntax as DCount but,
using the word DLookUp, in place of Dcount.
...and changing the first argument, to the field that
has the info for the message box.
But, Dlookup will only return One Record.
Which is perfect if that's what you want.

Otherwise as hwkranger suggested.

Dim rec As New Adodb.recordset
Dim strMsg As String

rec.Open "SELECT txtDate, txtSale, txtPosition FROM...", _
CurrentProject.Connection, adForwardOnly, adReadOnly

strMsg = GetString(adClipString,"; ",vbcrlf)

MsgBox "Conflicts; " & strMsg

rec.Close: set rec = nothing


...check my GetString() syntax?
 
The GetString fires the following error:

'Sub Or Function Not Defined'

Below is the code...

If DCount("*", "qryScheduleAvailable", "(#" & Me.txtScheduleStart + TimeValue("00:01") & "# Between [ScheduleStart] And [ScheduleEnd]) OR (#" & Me.txtScheduleEnd + TimeValue("23:59") & "# Between [ScheduleStart] And [ScheduleEnd])") > 0 Then

Dim rec As New ADODB.Recordset
Dim strMsg As String

rec.Open "SELECT * FROM qryScheduleAvailable", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
strMsg = GetString(adClipString, "; ", vbCrLf)


MsgBox "Conflicts: " & strMsg
Me.txtScheduleStart.SetFocus
DoCmd.CancelEvent
Exit Sub
End If


I want to be able to pull fields from the query to make a custom message box message.

Thanks,
David
 
Prefix with the recordset - GetString is a method of the ADO recordset object

[tt]strMsg = rec.GetString(adClipString, "; ", vbCrLf)[/tt]

Roy-Vidar
 
I now get the following message:

Error -2147217904
No value given for one or more required parameters.
 
If your stored query have parameters, you will need to resolve them.

Here is something I've posted somewhere else, illustrating how one can do such with parameterized stored queries with ADO. Assuming you have a text parameter called NameOfParameter of datatype text.

[tt] Dim cn As ADODB.Connection
Dim rs AS ADODB.Recordset
Dim cmd As ADODB.Command
Dim Prm1 As ADODB.Parameter

Set cn = CurrentProject.Connection

Set cmd = New ADODB.Command
With cmd
Set .ActiveConnection = cn
.CommandText = "qryScheduleAvailable"
.CommandType = adCmdStoredProc

Set Prm1 = .CreateParameter("NameOfParameter", adVarWChar, _
adParamInput, 255)
.Parameters.Append Prm1
Prm1.Value = strYourString

' for forwardonly/readonly recordset
Set rs = .Execute

End With

' for other cursor/locktype
' Set rs = New ADODB.Recordset
' With rs
' .LockType = adLockOptimistic
' .CursorType = adOpenKeyset
' .Open cmd
' End With [/tt]

For other parameter types

Long Integer:
.CreateParameter("NameOfParameter", adInteger, adParamInput)
DateTime
.CreateParameter("NameOfParameter", adDate, adParamInput)

Should they be form controls, you could also just do
.Parameters("[Forms]![frmMyForm]![txtMyControl]").Value = [Forms]![frmMyForm]![txtMyControl]
in stead of all the createparameter thingies.

For even more methods, you might check out this link


Roy-Vidar
 
I have tried this....but I get a compile error.....'invalid use of property' at the red text below.

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim Prm1 As ADODB.Parameter

Set cn = CurrentProject.Connection

Set cmd = New ADODB.Command
With cmd
Set .ActiveConnection = cn
.CommandText = "qryScheduleAvailable"
.CommandType = adCmdStoredProc
.Parameters ((((tblSchedule.ScheduleID) <> 0) And ((tblSchedule.RoomId) = [Forms]![frmSeries]![txtRoomId]) And ((tblSchedule.ScheduleDate) = [Forms]![frmSeries]![txtScheduleDate]) And ((tblSchedule.ScheduleStatus) <> 7)) Or (((tblSchedule.ScheduleID) <> [Forms]![frmSeries]![txtScheduleID]) And ((tblSchedule.RoomId) = [Forms]![frmSeries]![txtRoomId]) And ((tblSchedule.ScheduleDate) = [Forms]![frmSeries]! [txtScheduleDate][/color red]) And ((tblSchedule.ScheduleStatus) <> 7)))
Set rs = .Execute

End With
 
I'm assuming what you have posted is the entire where clause of the stored query. Then the parematers part could perhaps look like

[tt].Parameters([Forms]![frmSeries]![txtRoomId]).Value = _
[Forms]![frmSeries]![txtRoomId]
.Parameters([Forms]![frmSeries]![txtScheduleDate]).Value = _
[Forms]![frmSeries]![txtScheduleDate]
.Parameters([Forms]![frmSeries]![txtScheduleID]).Value = _
[Forms]![frmSeries]![txtScheduleID][/tt]

Roy-Vidar
 
Here is what I have so far...

How do I grab the fields and use them for the message box that should fire?



If DCount("*", "qryScheduleAvailable", "(#" & Me.txtScheduleStart + TimeValue("00:01") & "# Between [ScheduleStart] And [ScheduleEnd]) OR (#" & Me.txtScheduleEnd + TimeValue("23:59") & "# Between [ScheduleStart] And [ScheduleEnd])") > 0 Then

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim Prm1 As ADODB.Parameter

Set cn = CurrentProject.Connection

Set cmd = New ADODB.Command
With cmd
Set .ActiveConnection = cn
.CommandText = "qryScheduleAvailable"
.CommandType = adCmdStoredPro
.Parameters([Forms]![frmSeries]![txtRoomId]).Value = [Forms]![frmSeries]![txtRoomId]
.Parameters([Forms]![frmSeries]![txtScheduleDate]).Value = [Forms]![frmSeries]![txtScheduleDate]
.Parameters([Forms]![frmSeries]![txtScheduleID]).Value = [Forms]![frmSeries]![txtScheduleID]

End With


Exit Sub
End If
 
I'm terribly sorry, it seems I'm mixing techniques here. For DAO the above should work, but when testing now, it doesn't seem to work for ADO.

But did you try with

[tt] Set rs = .Execute
strMsg = rs.GetString(adClipString, "; ", vbCrLf)[/tt]

If not, here's another ADO thingie in stead - it's a little bit slower, and needs 2002 or higher version, but should resolve parameters dynamicly

[tt] Set .ActiveConnection = cn
.CommandText = "qryScheduleAvailable"
.CommandType = adCmdStoredProc

For Each prm1 in .Parameters
prm1.Value = Eval(prm.Name)
Next prm1

' for forwardonly/readonly recordset
Set rs = .Execute
strMsg = rs.GetString(adClipString, "; ", [/tt]


Else one could opt for DAO

[tt] dim rs as dao.recordset
dim qd as dao.querydef
dim pr as dao.parameter
dim strmsg as string

set qd=currentdb.querydefs("qry_Transactions")
for each pr in qd.parameters
pr.value=eval(pr.name)
next pr
set rs=qd.openrecordset()
do while not rs.eof
strmsg = strmsg & rs.fields("YourField").value & vbNewLine
rs.movenext
loop[/tt]

or perhaps

[tt] dim rs as dao.recordset
dim qd as dao.querydef
dim pr as dao.parameter
dim strmsg as string

set qd=currentdb.querydefs("qry_Transactions")
.Parameters([Forms]![frmSeries]![txtRoomId]).Value = _
[Forms]![frmSeries]![txtRoomId]
.Parameters([Forms]![frmSeries]![txtScheduleDate]).Value = _
[Forms]![frmSeries]![txtScheduleDate]
.Parameters([Forms]![frmSeries]![txtScheduleID]).Value = _
[Forms]![frmSeries]![txtScheduleID]
set rs=qd.openrecordset()
do while not rs.eof
strmsg = strmsg & rs.fields("YourField").value & vbNewLine
rs.movenext
loop[/tt]

Roy-Vidar
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top