I'm using Access 2007 on Windows Vista and am having some problems trying to configure a simple button. There are two tables called Clients and Contacts which are linked on clients.id and contacts.client _id. There are two forms associated with each table and I am trying to create a button to navigate from the Clients form to the Contacts form (which is a split form) but only to display the contacts where the current clients.id = contacts.client _id (eventually I would like the entry in the top of the form to be the contact with the earliest letter in the alphabet as their surname).
I turned on the Control Wizard and created the button specifying Form Operations ->Open form ->Contacts->Open the form and find specific data to display. At this stage there are no fields listed under the Clients table so I can't use the <-> button to link the fields. I have therefore tried to set up an On Click events procedure using DoCmd.OpenForm. I am not a programmer so have tried copying code from the Help and from forums such as this. My current code reads as follows (although I have tried many variations):
Private Sub Display_Contact s_for_Client_Cl ick()
DoCmd.OpenForm "contacts", , , _
"id = forms![contacts]![client_id]"
End Sub
Upon clicking the button the Contacts form is opened but with no records displaying. Other variations on the syntax either result in run time errors, or the Contacts form being opened with all of the records displaying at the bottom of the screen.
Any help much appreciated. I have set the properties for the MSACCESS.exe to run the program as administrator as I've read that this should be set for Access 2007.
Thanks, Emma
6 8799
Hi. You will need to refer to the value of the field client_id, not its name, within the Where string. If the client_id field is a number use the first version below, and if a string use the second version: - "id = " & forms![contacts]![client_id]
or - "id = '" & forms![contacts]![client_id] & "'"
Welcome to Bytes!
-Stewart
Hi Stewart, thanks for your welcome & your help...I'm tearing my hair out with this - a seemingly straightforward database configuration project is fast turning into a time consuming, frustrating nightmare!
The client id field is numeric so I tried your first example of code but when I click on the button I get run time error 2450 and a message saying the Contacts form can't be found - I promise you it does exist!
Any further suggestions gratefully received!
Ah, I should have spotted this earlier - you appear to be trying to open form Contacts with a WHERE clause based on a control value on the same form!! That simply cannot work, and indeed explains the error message you last received, as the control concerned cannot be referenced until AFTER the form is opened - which OpenForm cannot do because of the reference to the form's ClientID control which is not yet available (until the form is open). Do you see the circular reference?
I presume you have a client ID field somewhere on the form from which you are trying to open form Contacts. If you do, you can refer to that instead, substituting the actual name of your client ID control in the expression below: - "[ID] = " & me![your client ID control name]
-Stewart
Ah, I see what you mean. I was presuming that because I had created a relationship between the two tables (linked on clients.id and contacts.client _id) that the condition would work. I have now added an invisible field for contacts.client _id to the Clients form and amended the code to read:
DoCmd.OpenForm! "Contacts", , , " ID = " & Me![Client_id]
When I click on the button now it does open the Contacts form but it is blank.
Maybe I am going about this in totally the wrong way!
Thanks for your patience.
Emma
It appears to me that you are thinking of the relationship the wrong way round. If you are trying to open the Contacts form then it is the ID field underlying the Contacts table that you need to include in the WHERE clause, and the Clients ID field value that you need to supply to it to match from one to the other. This is the reverse of what you appear to be doing.
If I can try to clarify, the idea is that you - OPEN the contact form WHERE
-
the primary key field from the contact form's recordsource table
-
is equal in value to
-
the primary key field from the client form's recordsource table
This is more likely to be something like - DoCmd.OpenForm "Contacts" ,,, "[Client_ID] = " & Me![ID]
if I am correctly understanding which key is which...
-Stewart
Stewart - you have saved the day! Thank you so much, I really appreciate your help & patience. Now I'll have a go at configuring a button to do the reverse from the Contacts form back to Clients.
Emma
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Iain Miller |
last post by:
Now this shouldn't be hard but I've been struggling on the best way as to
how to do this one for a day or 3 so I thought I'd ask the assembled
company.....
I'm writing an application that tracks a group of Sales people, the
customers they deal with and the business they transact with them.
I've got my head around all the tables & some of the basic Query structures
OK and am beginning to delve into creating the forms I need to be able...
|
by: DataBard007 |
last post by:
My Access 97 database has a form that contains text boxes that display
people's names, addresses, phone numbers, etc. The record source is a
single table.
I created a NextRecord and Previous Record command button that, when
clicked, will display the next record and the previous record,
respectively.
But what if I want a quick glance at all my records? Is there a way to
get the records to display automatically, one after another, with a...
|
by: Lyn |
last post by:
Hi,
I am working on a genealogy form. The only table (so far) lists everybody
in the family, one record per person. Each record has an autonum ID.
The parent form (frmMainForm) displays the data in each record, which
includes the ID of the father and the mother (who also have records in the
table). One record per form.
I have a Tab Control in the form, and in one of the tabs I have a subform
(sfmSiblings) in which I wish to list...
|
by: keith |
last post by:
This may seem simple, but I'm having a bit of trouble figuring out
exactly how to do it. I'm accessing a database through an ODBC link,
and I have a query that returns only jobs completed that day (it's
automatically generated). 1 user may finish 10, 20, or 50 jobs a day,
but there can be at least 20-50 users per day, and they're always going
to be different. The users aren't going to return, or if they do, it's
always going to be under...
|
by: Susan Bricker |
last post by:
Greetings. As a relative newcomer to Access, I am having trouble
deciding on how to design the form flow for updating and creating
related records. I'm looking for a variety of suggestions so that I can
decide what's best for the user and what's best for screen flow.
Here's the structure: I have what's called "an Event". Each Event can
have multiple "Trials". Each "Trial" can multiple "Classes".
(This is the structure for a dog...
| |
by: Kevin |
last post by:
A couple of easy questions here hopefully. I've been working on two
different database projects which make use of multiple
forms.
1. Where's the best/recommended placement for command buttons for things
like delete, save, edit, cancel buttons - in the footer, or on the form
detail section?
2. If in the footer, how do you add them to the tab order?
|
by: emc_cos |
last post by:
I have a very nice parent child relationship happening between a main
form and 8 subforms.
The main form is the data entry point for a table that holds textual
data like names, notes and the like. It also holds the primary key
which is an alpha-numeric file designation.
The subforms are the data entry point for another table that holds
numeric values for time spent on tasks.
|
by: Tony |
last post by:
I'm working on project that plays movies using Windows Media Player and
I'm controlling everything with JavaScript. Per the client I only need
to support IE 6 or greater which happens to make things a bit easier.
What I need to do is create a playlist and play it using JavaScript. I
keep on getting close but not close enough to play the dang files. Has
anyone done this before and can shed some light on what worked for them?
|
by: tech.rawsteak |
last post by:
I have a function that retrieves a user's login name from their
workstation and looks it up on an employee table to return their full
name (ie: jsmith -John Smith). Their full name is then displayed on
each form as a greeting, while their login name is used to record
their activities, as well as compared to a list of positions that
allows different users to access different functions on the database.
I have noticed two things however:
...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| | |