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

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

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

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

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

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

Its not working MC whats ur idea ...

Learn as you can..
------------------------
pap...

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

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