Form help

[bilbonvidia]

Hi all, I am new here and new to db building so please be nice.

I have a table and have made a form for it. On the form I have customer name email and telephone. I created a second table to hold these details.
I put a combo box for name on my main form which links to the names in the 2nd customer table.

What I'd like to do is have it so that when a name is selected in this combo box their email and telephone is automatically displayed on the form. How do I do this?

 

[ColinEssex]

Firstly, your form should have a query as the recordset and not the actual table, you should always have a query as a forms recordset, its much more flexible.

Next, you mention the email and phone are in a second table. . . . . why? - whats in the first table thats bound to the form?

Next, how are your tables linked? - if its by name then change it. It should be linked by an AutoNumber ID. Linking by name is not good, names can be changed and you lose the link.

Col

 

[bilbonvidia]

I am trying create a very basic problem problem logging system. problems to be recorded on table one via the main form. I only created a second table so the person with the problem could be selected from a drop down list combo box which links to the customers in the second table. The second table also has the email address and phone as well as the customer name.

 

[bilbonvidia]

Okay, after looking at another DB I Have come up with this:

Private Sub Name_AfterUpdate()
' Update Phone/email text box based on value selected in CustomerID combo box.
Me!Telephone = Me![Name].Column(1)
Me!Email = Me!.Column(2)

End Sub

The telephone text box is updated with the correct phone numer according to the name selected in the combo box but I can't get the email text box to update, what is the correct syntax?

 

[Mile-O]

I am trying create a very basic problem problem logging system.

This should, by all accounts, be an easy thing to achieve. But, by reading the next passage, it's clear that you are over complicating matters due to a lack of understanding in normalisation process. Thus you are duplicating stuff across tables and whatnot.

To my mind, what you want is to create the following tables:

tblEmployees
EmployeeID (Autonumber)
Forename (Text)
Surname (Text)
EmailAddress (Text)
Telephone (Text)

tblProblems
ProblemID (Autonumber)
EmployeeID (Number)
ProblemTypeID (Number)
StatusID (Number)
ImportanceID (Number)
DateLogged (Date)
DateResolved (Date)

tblProblemType
ProblemTypeID (AutoNumber)
ProblemTypeDesc (Text)

tblStatus
StatusID (AutoNumber)
StatusDesc (Text)

tblImportance
ImportanceID (AutoNumber)
ImportanceDesc (Text)

Where:

Table Name
Primary Key
Foreign Key
Field Name
(Field Type)

• The Employees table holds information about, you guessed it, employees.
• The Problems table holds all the information about cases reported to this helpdesk.
• The ProblemType table holds a list of different summary level problem type (e.g. Login Trouble, Forgotten Password, Software Request, etc.)
• The Importance table holds a list of different levels of urgency for reported problems. Thus your users can log a problem as per your defined levels: e.g. Urgent, Important, Moderate, Whenever
• The Status table holds a list of different positions for the reported problem. Thus you can keep track of a problem's status. e.g. Not Yet Open, Investigating, Handling, Awaiting External Assistance, Completed, etc.

Once you had these tables created, you would open the Relationships window and join like for like named field. e.g. EmployeeID to EmployeeID

Now that you have your relationships in place, the next step would be to make a query based on each table. Within these queries you can sort and manipulate the data anyway you wish. For example, when I use Employee tables I like to concatenate my employees' forenames and surnames into one field in the following format: Smith, John. Easy to find, easy to read. You can't do this by linking a table to a form, just as you can't restrict the number of records loaded into a form when using a table.

Then make forms based on your queries. The important forms, of course, will be for the Problem Reporting screen. But that shouldn't stop you from using a switchboard to add in other forms for admin functions such as reviewing problems, editing employee details, and even problem statistics, should you wish.

 

[bilbonvidia]

Thanks for that, I have created another DB as you specified above, tables then the querys. I am now attempting to fathom how to create a form using the querys that Have a combo box to select the person with the problem, and have txt boxs for phone and email which will auto update depending on the person in the combo box. Txts boxes to enter date logged and closed. Combo box to select problem type. A text box to write specifics of the problem and the resolution. Then combo boxes for importance and status. I have tried using the form wizard but I am having trouble getting the form working how I would like it to.