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 October 10th, 2003, 02:10 AM
Authorized User
 
Join Date: Oct 2003
Posts: 53
Thanks: 0
Thanked 0 Times in 0 Posts
Default

hi,

too few parameters : does it also means that i have to have all the textfields on my form from the tables???

which means if i have 13 fields in my table, i will have to have 13 unbound textfield in my form???

is that so??? becoz the one that i am using is, there is actually more fields in my table as compared to my form???

coz i have checked every fields to see if it tallys or i have missed out anything but to no help, the error is still there. i really have no idea how to continue.

pls advice.
nono
 
Old October 10th, 2003, 10:49 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

The only controls you need on your form are fields you are assigning values to:

txtNRIC = rst!NRIC
txtName = rst!Name
txtAddress = rst!Address
txtContactNo = rst![Contact No]
txtDateOfNotice = rst!DateofNotice
txtStatus = rst!Status
txtReplyDate = rst![Reply Date]
txtAccountNo = rst![Account No]
txtAccountType = rst![Account Type]
txtSalary = rst!Salary
txtRemarks = rst!Remarks
txtMemo = rst!Memo
txtActionTaken = rst![Action Taken]

Copy the SQl below and paste it into your query designer SQL view and run it. Change the NRIC number to a valid NRIC number. Does it work? There is no point in embedding SQL in your code if you can't get it to run first as a standard Access query definition. Build your query in the query designer first, then paste the SQL into your module and format it as a string.

SELECT
    C.NRIC,
    C.Name,
    C.Address,
    C.[Contact No],
    C.DateOfNotice,
    C.Status,
    CA.[Account No],
    CA.Salary
FROM
    Customer_Account_Table As CA
INNER JOIN
    Customer_Table As C
ON
    CA.NRIC = C.NRIC
WHERE C.NRIC=1;

Don't add any more fields to your form until you can get something simple to run first. Then build.



 
Old October 10th, 2003, 12:51 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Could it be that

strWhereClause = "[c.NRIC] = " & Me!txtNRIC
should be
strWhereClause = "c.NRIC = " & Me!txtNRIC



Sal
 
Old October 10th, 2003, 02:44 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Hi Sal,

The brackets are basically ignored. It works either way.

Bob

 
Old October 10th, 2003, 03:26 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Hi nono,

The other thing to bear in mind is that with the current setup using a single, columnar form, you'll only be able to see the last (highest, sorted by primary key) account number for each customer displayed in the form. I'm assuming you are using a 1-to-many relationship between the customers and customer accounts tables. If customers can have mutiple accounts, and you want to view all of them when you search for a customer, you'll need to introduce a 2nd form (subform) or listbox control to get the master/child view.

Viewing the last account may be fine for you, I don't know. On the other hand, you may want to consider other options if you are combining tables in a query.

Regards,

Bob

 
Old October 12th, 2003, 09:45 PM
Authorized User
 
Join Date: Oct 2003
Posts: 53
Thanks: 0
Thanked 0 Times in 0 Posts
Default

hi,

thanks for all the help given. i have solve the error.
the error is due to :

strWhereClause = "c.NRIC = " & Me!txtNRIC
should be in this format
strWhereClause = "c.NRIC = '" & Me!txtNRIC & "'"


cheers,
nono

 
Old October 12th, 2003, 11:09 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Hi nono,

Remember my question 21 posts back when I asked:

"Which field is your search criteria ... and what's its data type?"

Well, I just got my answer.

Then 20 posts back I mentioned that I was using an integer...

If your NRIC field data type is an integer (number) you would use:

strWhereClause = "c.NRIC = " & Me!txtNRIC

If your NRIC data type is a string (text) you would use:

strWhereClause = "c.NRIC = '" & Me!txtNRIC & "'"

Glad its working for you. :)

Regards,

Bob

 
Old October 12th, 2003, 11:27 PM
Authorized User
 
Join Date: Oct 2003
Posts: 53
Thanks: 0
Thanked 0 Times in 0 Posts
Default

anyway really thanks!!!!!!
thanks for the help given so that i could work this out!!

cheerS!!!

 
Old October 12th, 2003, 11:45 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

No problem nono. I was just kiddin' around. Glad to help :)

Bob

 
Old October 13th, 2003, 04:38 PM
Authorized User
 
Join Date: Sep 2003
Posts: 52
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Nono,

I noticed a question in early on in this topic and wanted to point something out but never had the time. It's in regards to you set your controls to disabled and they we no longer as readable. If you also set you locked to Yes, it should display again as you originally set it.

HTH
-Roni

Roni Estein
Roni.Estein@e-drugscanada.com
https://www.e-drugsCanada.com





Similar Threads
Thread Thread Starter Forum Replies Last Post
Adding a search function Brendan Bartley ASP.NET 1.0 and 1.1 Basics 1 September 30th, 2008 03:40 PM
Search function talktome Excel VBA 1 September 6th, 2005 12:47 AM
search function elania MySQL 1 February 6th, 2005 07:00 PM
Search function Urban_Roughneck Beginning PHP 9 January 21st, 2005 08:31 AM
Search Function rylemer Pro VB 6 1 March 24th, 2004 11:37 PM





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