|
Subject:
|
Auto Messaging
|
|
Posted By:
|
anukagni
|
Post Date:
|
8/22/2006 6:08:46 AM
|
Hello Guy's
I got an Serious problems please give me some solution...
Iam having an database in that i got an field name called Name of the Equipment.. Now what i want is the Name of the Equipment is used by three (3) tables ..and iam using the Name of the Equipment to generate report the fact is one equipment name as entered in one table may not entered in another tables.. OK when the user is select an Name of the Equipment they will get the report if the record is not matching then an message should display record not found...
pls give me an idea to solve this issue..
if ur not able to understand plzz revert me
Learn as you can..
------------------------
pap...
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
8/22/2006 6:38:54 AM
|
It sounds like what you want to do is to allow the user to select "Name of the Equipment" in a combo box, and then run a report.
If that is the case, then you can put code on the On No Data event of the report like this:
'----------
Dim gfReportHasNoData As Boolean
gfReportHasNoData = True
Cancel = True
'----------
This will prevent the report from being opened, and give a msgbox that says "The Open Report action was cancelled". That may not be enough info for you.
You can also make a call to the data first, and if the recordcount <> 0 then open the report (of form) else pop up your own message stating there are no records.
Does this help?
mmcdonal
|
|
Reply By:
|
anukagni
|
Reply Date:
|
8/23/2006 1:47:26 AM
|
Okey i got the idea but the fact is iam using the combo box as u said and when the option selected and the command button clicked then the read only query view will appear..what should i do in the case..?
thanks
Learn as you can..
------------------------
pap...
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
8/23/2006 6:42:02 AM
|
There may be a more elegant way of doing this, but I would try this:
(This assumes that the Combo Box is binding to a Primary Key field, and not the text name of the equipment. Also, I don't know the name of the sub table you are pulling from, hence "tblSubTable", and you may also want to run a different query other than selecting just the name of the equipment in the subtable. But it looks like this is the only parameter.)
'-----------------
Dim rs As ADODB.Recordset
Dim iPK As Integer
Dim sSQL As String
If IsNull(Me.ComboBox) Or Me.ComboBox = "" Then
MsgBox("Please select Name of Equipment")
Exit Sub
Else
iPK = Me.ComboBox
End If
sSQL = "SELECT * FROM tblSubTable WHERE [Name of EquipmentID] = " & iPK
Set rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection
rs.Open sSQL
If rs.RecordCount <> 0 Then
'Commands to Open Query
Else
MsgBox("There are no records to display")
End If
'----------------
Put this code on the On Click event of your button, and put the existing code in the space that says " 'Commands to Open Query" including variable declarations etc.
Does this help?
mmcdonal
|
|
Reply By:
|
anukagni
|
Reply Date:
|
8/24/2006 1:43:52 AM
|
Ok but what i did is the combo box is having the source data from an table and in the Query i have given the criteria as [Form]![From Name].[comboboxname]
with ur explanation i have put the table name in the place of tblsub table .am i right i ill try and reply u..and what should i do when i got two combo box to select to get the read only query view?
thanks for ur attentation mc..
Learn as you can..
------------------------
pap...
|
|
Reply By:
|
anukagni
|
Reply Date:
|
8/24/2006 4:22:38 AM
|
quote:
Originally posted by anukagni
Ok but what i did is the combo box is having the source data from an table and in the Query i have given the criteria as [Form]![From Name].[comboboxname]
with ur explanation i have put the table name in the place of tblsub table .am i right i ill try and reply u..and what should i do when i got two combo box to select to get the read only query view?
thanks for ur attentation mc..
Learn as you can..
------------------------
pap...
I have tried but i got the runtime time error..
iam doubting on the sSQL statement which i entered
U have given the statement as
sSQL = SELECT*FROM tblSubtable WHERE [Name of the EquipmentID] = " &iPK
But the problem is iam not using any subtable and any Primary Keys..
I am usind an table for the source and an query for the report in the query view.
So I entered as
sSQL = SELECT*FROM Sourcetable WHERE the [Nameof the EQuipment field]"& iPK
But i got an error as
# Run time Error '-2147467259(80004005)': #
# Metho 'Open' of object'_Recordset' failed #
its headaching!
Learn as you can..
------------------------
pap...
|
|
Reply By:
|
Figgis
|
Reply Date:
|
8/24/2006 9:06:08 AM
|
I'm not sure about using data from a combo box but if you are building an SQL statement that has a string you are using to compare against then it has to be in single quotes or it will error. I could be completley wrong but I would try changing the SQL string to
sSQL = "SELECT * FROM tblSubTable WHERE [Name of EquipmentID] = " & "'" & iPK & "'"
|
|
Reply By:
|
anukagni
|
Reply Date:
|
8/28/2006 12:19:34 AM
|
Its not working MC whats ur idea ...
Learn as you can..
------------------------
pap...
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
8/28/2006 6:17:23 AM
|
This is the SQL string you posted. Here is what is wrong with it:
sSQL = SELECT*FROM Sourcetable WHERE the [Nameof the EQuipment field]"& iPK
If iPK is an integer, it should be:
sSQL = "SELECT*FROM Sourcetable WHERE [Nameof the Equipment]="& iPK
If iPK is a string, it should be:
sSQL = "SELECT*FROM Sourcetable WHERE [Nameof the Equipment]="& "'" & iPK & "'"
Does this help?
mmcdonal
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
8/28/2006 6:18:23 AM
|
Actually, it also looks like you are missing spaces, so:
sSQL = "SELECT * FROM Sourcetable WHERE [Nameof the Equipment]="& iPK
or
sSQL = "SELECT * FROM Sourcetable WHERE [Nameof the Equipment]="& "'" & iPK & "'"
mmcdonal
|
|
Reply By:
|
anukagni
|
Reply Date:
|
9/1/2006 11:54:10 PM
|
it's on the samw status mc...
Learn as you can..
------------------------
pap...
|
