Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 22nd, 2006, 06:08 AM
Friend of Wrox
 
Join Date: Dec 2005
Posts: 233
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to anukagni Send a message via Yahoo to anukagni
Default Auto Messaging

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...
 
Old August 22nd, 2006, 06:38 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old August 23rd, 2006, 01:47 AM
Friend of Wrox
 
Join Date: Dec 2005
Posts: 233
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to anukagni Send a message via Yahoo to anukagni
Default

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...
 
Old August 23rd, 2006, 06:42 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old August 24th, 2006, 01:43 AM
Friend of Wrox
 
Join Date: Dec 2005
Posts: 233
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to anukagni Send a message via Yahoo to anukagni
Default

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...
 
Old August 24th, 2006, 04:22 AM
Friend of Wrox
 
Join Date: Dec 2005
Posts: 233
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to anukagni Send a message via Yahoo to anukagni
Default

Quote:
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...
 
Old August 24th, 2006, 09:06 AM
Authorized User
 
Join Date: Oct 2005
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Figgis
Default

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 & "'"
 
Old August 28th, 2006, 12:19 AM
Friend of Wrox
 
Join Date: Dec 2005
Posts: 233
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to anukagni Send a message via Yahoo to anukagni
Default

Its not working MC whats ur idea ...

Learn as you can..
------------------------
pap...
 
Old August 28th, 2006, 06:17 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old August 28th, 2006, 06:18 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Messaging in JAVA salahu J2EE 0 July 22nd, 2006 11:01 PM
Messaging in JAVA salahu BOOK: Beginning PHP, Apache, MySQL Web Development ISBN: 978-0-7645-5744-6 0 July 22nd, 2006 10:59 PM
instant messaging with System.Messaging connect2sandep General .NET 35 March 1st, 2006 01:11 PM
sms messaging dev_sept ASP.NET 1.0 and 1.1 Professional 0 August 28th, 2005 11:55 AM
asynchronous messaging in .Net jimbeam36 .NET Web Services 0 November 9th, 2004 07:40 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.