Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
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 April 27th, 2007, 09:08 PM
Authorized User
 
Join Date: Apr 2007
Location: Fort Bliss, Texas, USA.
Posts: 65
Thanks: 0
Thanked 0 Times in 0 Posts
Default Form & Table issue

Ok, I may explain this wrong but I'll give it a whirl. Here it goes:

I have a table, tblContact. This table stores the first name, last name and phone number of the contact that would like to be called upon completion of their task. The data is stored in the respective fields.

However, the form that I am looking at is the frmTask. I put a spot in there for the individual to pick the contact from the list of contacts in the database. This will field on the form will need to pull the last name, first name (last name COMMA first name) of the conatct. Right now it just pulls the contact ID number (the primary key as an autonumber).

So I'm trying to figure out how to program this unbound text box to :

1. Open the 'frmSearchContact', where the user will search by last name or be prompted to enter as a new contact.
2. After finding the contact, when they click on 'insert', it will put the contact last name, first name in : Pickup Contact field.

Any idears? or did I not explain it right?

 
Old April 28th, 2007, 07:14 PM
Friend of Wrox
 
Join Date: Mar 2004
Location: Yorba Linda, California, USA.
Posts: 217
Thanks: 0
Thanked 1 Time in 1 Post
Default

Bryan,

So you want to record which contact person for each Task? How about if you make that Contact text box a combobox that is populated with the contact names? This eliminates a few of the steps but I think it may get you where you want to go: On the comboBox:

1) Set the record source to pull the contact name (ie: "SELECT lastname" & ", " & firstname FROM tblContact ORDER BY lastname" (you will need to use whatever you have named the firstname and lastname fields there)

2) Set the Columns Property to: 2

3) Set Column Widths Property to: "0"; 1.5"; (use 0 if you do not want the primary key to show, I'm only guessing at 1.5 inches on the lastname + firstname part of it- check to see what it looks like)

4) Bound Column should remain 1 (your PK is first in the SQL statement above and the value of the listbox will become the primary key of the selected record at runtime when the user clicks on it)

5) set the control source to what ever field you want to record the contact in.

Does that help?

Loralee

 
Old April 29th, 2007, 09:20 AM
Authorized User
 
Join Date: Apr 2007
Location: Fort Bliss, Texas, USA.
Posts: 65
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I'm going to try that because it seems like the easiest way. I was trying to make it so that the user would 'double-click' in the blank area, it would open the 'SearchForContact' query form. This would allow the user to search by the last name and it would display all the people in the db tblContact with that last name. When they saw the one they wanted, they'd just doubl-click it and it would automatically be pulled into the field on the frmTask. If not, they would have a command button on the "searchforcontact" form that allowed them to add a new contact.

Follow?

 
Old April 29th, 2007, 11:14 AM
Friend of Wrox
 
Join Date: Mar 2004
Location: Yorba Linda, California, USA.
Posts: 217
Thanks: 0
Thanked 1 Time in 1 Post
Default

Bryan,

Another thought that adds on a means of handling new contacts to what I described: You could add a "New" button that would open a frmContact (to populate tblContact with firstname, lastname, phone number- You did not mention any other fields for each contact but if you have them you would want to populate them there probably).

Also, how many contacts do you have or anticipate having? (would the normal functionality of a combobox take care of the search by last name requirement? ) Also, will the user be entering alot of unique contacts after you launch your app?

 
Old April 30th, 2007, 09:31 AM
Authorized User
 
Join Date: Apr 2007
Location: Fort Bliss, Texas, USA.
Posts: 65
Thanks: 0
Thanked 0 Times in 0 Posts
Default

The contacts, well, I am going to say will stand about at 100, but the user can also delete the contact if they leave the job. I am putting a command button on the main form for the user to enter a new contact in, just figured that it could also be done through the main form where most information / all information will be done. That's why I wanted it to double-click, open the searchcontact query, then the user could double-click if the contact was there. If not, the same command button that is on the main form (add new contact) will be on the search query form to add it from there should they not exist.

 
Old April 30th, 2007, 11:18 AM
Authorized User
 
Join Date: Apr 2007
Location: Fort Bliss, Texas, USA.
Posts: 65
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Loralee,

I put a list box, rather than a combo box. I made it display like LastName, FirstName, Phone. Now I have to figure out how to make the 'Search for Contact' query and display the fields like a form I made ages ago.

I had a form search by keyword (you'd enter the last name). Then below it there was an area where it would display the results. You'd highlight one, click 'open' and then it would open the form. On this one though, I'd have to make it 'insert' the selected field into the listbox on the parentform.

 
Old April 30th, 2007, 08:04 PM
Friend of Wrox
 
Join Date: Mar 2004
Location: Yorba Linda, California, USA.
Posts: 217
Thanks: 0
Thanked 1 Time in 1 Post
Default

Bryan,
A few thoughts:
"just figured that it could also be done through the main form where most information / all information will be done. That's why I wanted it to double-click, open the searchcontact query, then the user could double-click if the contact was there." You COULD put it all on the main form. I'm only suggesting to use a combobox on the main form so you don't have to have extra fields on it that you may not need to show the user at the same time. And you don't have to launch a new form each time to enter a contact, so less clicking around. Just choosing a contact that is already in the table could populate the related fields if you want that. Do you want all the contact fields on the main form?

I assume a contact can be associated with several tasks, right? And how often are you needing to enter a new contact? (5% of the time?, 50% of the time?)

2) "I had a form search by keyword (you'd enter the last name)" Are you wanting to search ONLY by lastname? And part of the lastname or the whole last name?

3) "Then below it there was an area where it would display the results" Is this a list box?

4) "I'd have to make it 'insert' the selected field into the listbox on the parentform." You could have it save the new contact to the table then refresh the main form. That will cause the listbox to populate with the new name.

HTH,

Loralee



 
Old April 30th, 2007, 08:13 PM
Authorized User
 
Join Date: Apr 2007
Location: Fort Bliss, Texas, USA.
Posts: 65
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Loralee,

I am going to take a look at what you're saying tomorrow. I put a suggestion into WROX to tell them that they should allow people to upload files to the site for help as it would be a whole lot easier to explain problems or issues that people are having.

If I was able to do this, it'd make it so much easier for you to understand / figure out what I'm trying to do.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Populate a List Box with Table Names & Table date hewstone999 Access VBA 1 February 27th, 2008 10:10 AM
Table rendering with table & comboBox pazzuzu Java GUI 0 May 9th, 2007 09:04 AM
' = ' issue kgoldvas XSLT 2 February 28th, 2006 09:52 AM
JSP & Proxy Issue mmcgough JSP Basics 0 March 29th, 2005 11:05 AM
sql & join tables & find a field in multiple table trangd Beginning PHP 2 January 29th, 2004 07:18 PM





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