Form in a form

[aliceapraham]

I'm doing a form that has a subform in it from a table, then I want to put another table in that spasific form, how can i do that? cause when I'm puting a text box & in the control source I'm pressing the expression builder & clicking on the table that i want & the field that i want but it gives me an error, what is wrong with that picture. Can't i put 2 or 3 tables in one form.
I'm very new in access so pls help.
Alice

 

[MajP]

You can nest up to 2 subforms (Main form, subform, sub subform). You can not nest subforms on continous forms only single form views.

If you want to have a main form with a continous subform, and then another continous subform you can but the two continous subforms side by side one the main form and then synchronize them with a little code. I think that this is a nicer interface then multiple nested subforms.

 

[aliceapraham]

Pls will you be more spacific. Can I put couple of tables in One Form?
If 2 tables ae connected & one totally alone. Can i do that, & when the user use that form where will the data be saved?
Thank you for ur time

 

[MajP]

Yes using sub forms you can do that. The data is save into the table wherever the data comes from. Describe in words what kind of information you want to show, how you want to present it to the user, and how the user will want to see and interface with the data. Make sure to specify what information is viewed in continous form view, and what is viewed in single form view.

 

[aliceapraham]

I have Customer table with their info in
I have Sales Man table with their info in & they are related one to many.
Then I have a price table
Service price1(qty0-100) price2 (101-200)
Inkjet $10 $5
Sort $15 $10
Laser $20 $15

I need to make a form (Friendly user interface) that will be in it couple of drop down lists that contain the Company name (Customer) then automatically the sales person for that company will pop up. with the info that's in the table)
Than, which is the most important part:

Qty Service Price Total

THese fields should be in the form, I want the user to put the Qty in & from a combo box he selects the service & in the unit price according to the qty it put in the right amount then in the total field it will calculate the qty * Unit price (which is the easiest part).

 

[MajP]

can you describe your tables and important fields a little better, and which fields the tables are related by. Example

tblCustomers
customer_ID (the primary key)
customerName

tblPrice
qty
service
Price
...
fkSalesMan (relates the price to sales Man ?)

 

[aliceapraham]

TBLSalesMan has CustomerName & SalesPhone#. The problem is that tblPrice is not related at all to the previous tabls. don't know how i can relate them togather, although i have to have an Estimate # on the form which has to be uniq each time. so HELP

 

[TheAceMan1]

How are ya aliceapraham . . .

It may not be approriate for you at this time, but when you have a chance go over the folowing . . . it'll help us help you better:

Fundamentals of Relational Database Design

Normalizing Tables

Table Relationships

Also as an Idea of what you can do with subforms: faq702-5860 and as of access 2k subforms can be nested 3levels deep.

BTW: [blue]Welcome to Tek-Tips![/blue] To get great answers and know whats expected of you in the forums be sure to have a look at FAQ219-2884

See Ya! . . . . . .

 

[TheAceMan1]

[blue]The problem is that tblPrice is not related at all to the previous tabls.[/blue]

[blue]Not yet related in access[/blue] I can understand, is it related to any of the other tables as far as the scheme of things in your mind is concerned?

If not than what's its purpose?

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[aliceapraham]

The Price tbl is not related at all to the customer or salesman tbl, all i need is a form that has in it the drop down fields which the user should choose the customername & automatically the sales person for that customer will pop up, & in the bottom of the page i should have qty filed & a drop down field that has the services which the sales man(or my company) provide & according to that qty the unit price will pop up in the unit price & makes the calculation in the total field, really the price table doesn't have to be connected with the customer or sales the only thing is that THat form should have an # (i mean each estimation(or sheet) should have a new #.
And thank you for th esites, I'll check them. Alice

 

[TheAceMan1]

[blue]I have Customer table with their info in
I have Sales Man table with their info in & they are related one to many.[/blue]

[ol][li]Just to be sure is that [blue]one salesman[/blue] can have [blue]many customers[/blue]?[/li]
[li]Whats the name and data type of the [blue]primarykeys[/blue] in the tables?[/li]
[li]Whats the name and data type of the [blue]fields involved in the relationship[/blue] of the tables?
[li]Before we get started [purple]backup the DB under another name so you can come back to square one if necessary[/purple].[/li][/ol]
For starters you can use the [blue]form wizard[/blue] to setup your initial [blue]Salesman Form[/blue] with [blue]Customers Subform[/blue]. Don't panic! . . . when we get there this form combo will go on another mainform which will include your other controls. For now this has to be done.

Yoy'll know you have it right when you open the salesman form and change record . . . the customers subform will change accordingly. Let us know when this is accomplished or if you have trouble or need instructions . . .

See Ya! . . . . . .

 

[aliceapraham]

Ok I'm so frustrated here.
So here is the deal. I have tblCustomer: CustomerID (PrimaryKey)
CompanyName
CompanyAddress, city satate...etc
TblSalesman: SalesID (primary Key)
SlaesFirstName
SalesLastName
SalesPhone
CompanyName (which should be the same as in Customer Table)
And YES, each salesman has several companynames.
how can i relate them, should i make in the TBLcustomer a field called SalesID ? to make the realtion on to many?
Thank you again for your help.
Alice

 

[TheAceMan1]

aliceapraham . . .

[ol][li]For ease of development for yor primary keys for both tables should be Autonumber. If not already . . . make it so.[/li]
[li]Yes . . . add the field [blue]SalesID[/blue] to tblCustomer. Data type should be number [blue]Long Integer[/blue] [purple]Note: you were already missing a key field here![/purple][/li]
[li]You don't need [blue]Company Name[/blue] in tblSalesMan. Remove it![/li]
[li]Now make your one to many relationship in the relationships window.
Be sure to check the [blue]Enforce Refere4ntial integrity[/blue] checkbox and [blue]Cascade Update Related Fields[/blue] as well.[/li]
[li]Now run the [blue]form wizard[/blue] as I mentioned in my prior post. Initially just select all the fields from both tables. When you click next Youll be able to select form with subform, then step thru it . . .[/li][/ol]

[blue]Ok I'm so frustrated here . . .[/blue]

[blue]Alas . . . at least you can see the DB![/blue]

See Ya! . . . . . .

 

[aliceapraham]

Ok Sir, I guess it worked, I did the SalesID as primary & added it in the TBLCustomer, & did the form & subform & it looks good, but i need to convert those to combo boxes, as i wan the user to scrol down the company name find whatver he wants & automatically pops up the Salesman name. How can we do that? I am going step by step with you & appreciate your help very much. Thanks
Alice

 

[TheAceMan1]

aliceapraham . . .

Your table structure should be:

[tt][blue]tblSalesman
-----------
SalesID as Long Integer (PrimaryKey)
SlaesFirstName
SalesLastName
SalesPhone

tblCustomer
-----------
CustomerID as Long Integer (PrimaryKey)
[purple]SalesID as Long Integer[/purple]
CompanyName
CompanyAddress
city
State[/blue][/tt]

Note: tblCustomer.SalesID isn't/shouldn't be a primarykey. Its a field simply set to [blue]Long Integer[/blue] datatype. The only primarykey for tblCustomer is CustomerID.
[purple]Let me know if this is otherwise![/purple]

[blue] i wan the user to scrol down the company name find whatver he wants & [purple]automatically pops up the Salesman name.[/purple][/blue]

Is it possible for more than one salesman to be involved with the same customer . . . aka company?

Stated another way, is possible for two salesman to be involved with different customers from the same company?

Since one salesman can have many customers (via your current table structure)I hope you see my point!

[blue]Your Thoughts? . . .[/blue]

BTW what you ask takes advanced programming . . .

See Ya! . . . . . .

 

[PHV]

Why not have a look at the Northwind sample database that comes with ms-access ?

 

[aliceapraham]

Well I did what you told me to, SalesIS in TBL Customer IS a long Integet, & not a primary key.
And yes, 1 Salesman can have different customers so it is 1 to many relationship.
And yes I know that what i want is advance programming that's why I'm here suffering to do it. I knwo it requires couple of codes in the event procudure. but first I need to build the DBS. So pls help me.
1st Question. I need to put a control (combobox) in the form to show me the customers names & I select from them & automaticaly the salesman names pops on the Slesman field.
Let's go one step at a time to do this then I'll tell u what are my other problems. Thanks

 

[TheAceMan1]

aliceapraham . . .

. . . and my questions?

[blue]Is it possible for more than one salesman to be involved with the same customer . . . aka company?

Stated another way, is possible for two salesman to be involved with different customers from the same company?[/blue]

The indication here is that more than one salesman can be involved with the same company . . .

More than that with current table setup another form is required if you are to show all salesman involved with a company.

You really need to read the following (along with [blue]PHV's[/blue] suggestion):

Fundamentals of Relational Database Design

Normalizing Tables

Table Relationships

It'll help you, help us, help you! . . .

See Ya! . . . . . .

 

[aliceapraham]

believe me I'm reading it while I'm writing this to u.
And as I said, One Salesman has different Customer (Let's call it 'Company') like Alice (Saleman) has Tropicana Hotel (customer, or company), Alice also bring work from other companies, like Harrahs or Caesars... so IT IS One to many relationship.

 

[TheAceMan1]

aliceapraham . . .

Good enough. Lets see about the combobox!

The code requires [purple]Microsoft DAO 3.6 Object Library[/purple] to run. To [blue]check/install[/blue] the library, in any code window click [blue]Tools[/blue] - [blue]References...[/blue] In the listing find the library and [blue]make sure its checked.[/blue] Then using the up arrow, [purple]push it up as high in priority as it will go[/purple]. Click OK.

[ol][li]Open the [blue]SalesMan[/blue] form (mainform) in design view.[/li]
[li]Click the [blue]combobox wizard button[/blue]

then click inside the form where you wish to place it. When the [blue]Wizard Dialog[/blue] opens click Cancel.[/li]
[li]Set the following properties for the combobox:
[tt][blue] Column Count [purple]4[/purple]
Column Widths [purple]0";1";0";0"[/purple]
List Rows [purple]25[/purple]
List Width [purple]1"[/purple]
Row Source Type [purple]Table/Query[/purple][/blue][/tt][/li]
[li]In the [blue]RowSource of the combobox[/blue], copy/paste the following [blue]SQL[/blue]:

[blue]SELECT tblCustomer.SalesID, tblCustomer.CompanyName, tblSalesMan.SalesFirstName, tblSalesMan.SalesLastName
FROM tblSalesMan INNER JOIN tblCustomer ON tblSalesMan.SalesID = tblCustomer.SalesID
ORDER BY tblCustomer.CompanyName;[/blue]

[/li]
[li]On the [blue]Event Tab[/blue] select [blue]After Update[/blue] then click the three elipses

just to the right. If a [blue]Choose Builder Dialog[/blue] pops-up select Code Builder.[/li]
[li]Your now in the [blue]Visual Basic Editor[/blue] (VBE) and you should see the event as follows:

[blue]Private Sub [purple][b][i]ComboboxName[/i][/b][/purple]_AfterUpdate()

End Sub[/blue]

Copy/paste the following code in between the two lines above ([blue]you![/blue] substitute proper ComboboxName in [purple]purple[/purple]):

[blue]   Dim Criteria As String
   
   Criteria = "[SalesID] = " & [purple][b][i]ComboboxName[/i][/b][/purple].Column(0)
   Me.Recordset.FindFirst Criteria[/blue]

[/li]
[li]Click the [blue]save[/blue] button then hit [blue]Alt+Q[/blue] to return to design view.[/li]
[li]Save, close the form and do your testing . . .[/li][/ol]
[blue]Let me know how ya make out! . . .[/blue]

See Ya! . . . . . .