Main form and subform issues

[HGoodloe]

Hello everyone,

I’m sending this question out once again because I still haven’t been successful with my Main and sub form issues.

I have a main form that has a text box for contract number. When keying in the contract number, after pressing the command button, I would like for the data associated with that number to appear in the sub form. I know this is probably something fairly easy, but for some reason I can’t seem to figure out the correct syntax.

I’ve done numerous google searches but still no luck. In fact, google searches are may first choice in attempt to trying to obtain information relative to the projects I’m working on. Tek Tips, usually comes into play as a last resort such as in this case.

The text box name on the main form is contractNo and the records in the sub form associated with contractNo are as follows: DesNo, LettingDate and Section. I am aware that I could use a combo box instead, however in this case I have my reasons why I prefer to use a text box instead. I also know that what I’m attempting to do may very well be related to filtering, but again I’m having a mental blockage and can’t get it figured out.

Combo boxes are used in some of my other projects that are somewhat similar to this one, but for this particular form I prefer a text box. In other words I need the text box to behave in the same manner as a combo box.

Is there anyone out there that can help me with this?

Thank you

 

[MajP]

Did you try what I suggested? Link the subform to the text box. I beleive I gave a detailed answer.

 

[MajP]

http://www.4shared.com/file/83283791/7f705b23/Txt_box_Search.html

Here is a demo using no code. Type in the text box and the related orders appear in the subform.

 

[mfs81]

If you learn how to use code to do this instead of the access wizards it is in the long run much easier and more powerful.

Whatever command button you are using, go to properties (Event Tab) and find the OnClick. If you click to the right of the column (...) it should get you into the function associated with that button.

Assumptions:
** ContractNo are numbers!
1. TableName - is the table with those items in the subform
2. txtContractNo is the name of your textbox, i would rename instead of ContractNo to avoid confusion with the field
3. ContractNo is a field also in the table with the fields DesNo, LettingDate, Section
4. sfSubform is the name of your subform

Put this into the code
Dim strSQL as String

strSQL = "SELECT DesNo, LettingDate, Section FROM TableName where ContractNo = " & txtContractNo.Value

sfSubform.Form.Recordsource = strSQL
Me.Requery

What you are doing is setting the source of your subform to an SQL statement through code when you click that button.

Also if you want it to happen after you enter the text in the box, open the properties on the textbox and put the code in the OnEnter property.

 

[MajP]

http://www.4shared.com/file/83306800/1e1bff97/cmbo_box_Search2.html

This demo shows a much easier solution using a combo box. With the combo
1. you can type a number in if you want
2. it auto finds your number
3. it ensures you get a value from the list

And why do you not want a combo?

 

[TheAceMan1]

How are ya HGoodloe . . .

Hmmmmmmmmmm ... mmmmmmmmm ... Must answer questions:

Are the mainform and subform bound forms?
If Yes: are they linked with master/child properties?
If linked, whats the name of the linking field and its datatype numeric/text?


See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[HGoodloe]

In answering to the question, the form is bound to one table(tblcontracts).

The contract number text box is placed on the main form while the rest of fields are on the child form.

Yes, I did see master/child properties in the properties of the child form, but they didn't appear to be linked