Pull record info from subform to form 2

[RonnieDobbs]

I would like a form which has a combo box that searches for company information by name. For instance if I type in blue, company's like blue tech, blue tech AG, blue tech OY, etc..., are brought up a list in a subform. Thats not the problem. What I am wondering is, when the list of companies come up, is there a way to double click on a certain record, which will bring up a form displaying all the company's information?

thanks for the input,

AB

 

[OhioSteve]

Yeah there are many ways to do it. This would be my approach:

1) I would put a yes/no field called "selected" in the underlying table.

2) When the first form opens, I would run an update query that set selected = no for the whole table.

3) I would display the new field in the first form's subform. In the first form, I would also add a button with the capion "display record". This button would cause the second form to appear. That form would display the records where selected = "yes".

My approach is very easy to understand and implement. However, it does have two drawbacks. If the table had more than about 100,000 records, then it might take several seconds to open the first form (because the update query must run). If the database can have more than one user simultaneously, then its possible that one guy could deselect the record that the last guy just selected. There are ways to minimize the second problem if it concerns you.

 

[ZmrAbdulla]

Create a command button on the subform to open the popup form through the wizard with record maching criteria.
Then copy & paste the code(with appropriate changes)to the DblClick event of control on the subform.
This will do it

Zameer Abdulla
Visit Me
Where to pay your [purple]Tsunami[/purple] relief donations?

 

[RonnieDobbs]

thanks for those two options guys, I will give them both a try. Now I thought I would be able to easily create this search box displying companies in the subform but i thought wrong. How would I accomplish doing this:

many companies in the table. subform based on the company table but there could be different comapny names, example: bule inc, blue tec, blue plc., blue industries. I want to type in blue and have the subform displaying just the comapnies that have blue in their names???

 

[ZmrAbdulla]

Here is the sample created for you

Zameer Abdulla
Visit Me
Where to pay your [purple]Tsunami[/purple] relief donations?

 

[RonnieDobbs]

Thanks for the help Zameer, the link you gave did not contain anything for me to extract form winzip?

 

[OhioSteve]

The best way to reach your goal is to use wildcards. I have created an example that should put you on the right track:


1)I created table1 with the field f1. The field contained these records:

Cat
The Cat's Whisker
Garfield the cat
dog

2) Then I wrote this query:

SELECT Table1.f1
FROM Table1
WHERE (((Table1.f1) Like "*cat*"));

3) That query produced this output:

Cat
The Cat's Whisker
Garfield the cat

 

[RonnieDobbs]

What if I want to type in anything, besides cat. Is this query just for the word cat?

 

[OhioSteve]

You may be thinking "Gee that will work as long as the user wants to search for the string 'cat'. But my users may want to search using other strings." Indeed, this will be a problem unless they are real cat lovers. Luckily you can combine wildcards with references to fields on forms. Check out this:

SELECT Table1.f1
FROM Table1
WHERE (((Table1.f1) Like "*" & [Forms]![Form1]![Text0] & "*"));

In the field text0, I wrote "dog". Then I ran my query. It produced this output:

dog

And indeed, that is the value of the only record with the string "dog".

If you wanted to get more fancy, you could construct the query string in VB and inject it. But that is really not necessary.

 

[OhioSteve]

Oops, I was typing my second response while you were posting. I did not intend any disrespect.

 

[CDaly]

I am trying to develop a form that contains information from tables with more than one record and tables with each customer only once. I want to show just the most up to date information from the tables where there are more than one customer record.

Can this be done?

Thanks

 

[penndro]

How to Show Totals from Subform on MainForm?

I am trying to calculate the total Payments shown on a subform in an unbound Textbox of my main form. Can anyone provide some help on how to code this in VB?

Appreciate it,
Penndro

 

[RonnieDobbs]

Thanks guys for the help!

 

[OhioSteve]

CDaly,

Your question makes me wonder about the relational structure of your DB. However, I can help you to do what you want to do.

Here is an example for you~

Table1 contains these fields & records:

customerName transactionDate contactName
Bank of Smithville 6/6/2003 Joe
Allied Shipping 1/1/2000 Harold
Bank of Smithville 4/6/2003 Mary

query1 contains this sql:
SELECT Table1.customerName, Max(Table1.transactionDate) AS transactionDate
FROM Table1
GROUP BY Table1.customerName;

query2 contains this sql:
SELECT Query1.customerName, Table1.contactName
FROM Query1 INNER JOIN Table1 ON (Query1.transactionDate = Table1.transactionDate) AND (Query1.customerName = Table1.customerName);

Query2 produces output analogous to what you want.

 

[OhioSteve]

penndro,

You don't need VB to create the subtotals. Basically you just need to put =sum(fieldName) in the appropriate footer band of the form or report. You can find many examples of this in the Northwind DB. You should also review ACC2000 208835.

 

[ZmrAbdulla]

RonnieDobbs
The link is working fine for me. Here is the MDB right click and select Save Target As
please try it.
penndro,
Most of the form's procedures are in this db from Microsoft

The Microsoft Access 2000 sample forms database
regards

Zameer Abdulla
Visit Me
Where to pay your [purple]Tsunami[/purple] relief donations?