using a form to select another form

[lewie]

I need to generate a form with a certain record. What is the best way to select the data I want from a table and populate a form. for instance I have a vivendi client. I want to bring up a form or something to have them choose vivendi from maybe a combo box and then maybe hit a button and up comes the vivendi form filled out. i tried filling out the where column in a form equal to the value in the other form but it seems that it goes out of scope or something. Anyway any help will be greatly appreciated

 

[ordendelfai]

Hi Lewie,

The two easiest options you have are using

a. Filter
b. Criteria on forms source.

I am not sure how your table structure is set up so it's hard to give you a cut and paste solution. In the drop down box, I would have two columns. The first column would have the clients ID number, the second the clients name. The combo box would be bound to the first column, but would have zero width so it does not show up in the drop down box (just the name of the client).

In the OnClick event of the button (or the afterupdate event of the combo box), put something like:

Private Sub cmboID_AfterUpdate()

Dim ClientID As Integer

ClientID = Me.cmboID 'You would have to put the name of your combo box control name here.

DoCmd.OpenForm "frm_MyForm", , , "[ID] = ClientID" ' you would have to put the correct field name between the [] and put the correct name of your form.

End Sub


If your combo box will just hold text and in your new form you want the criteria on the client name field then you could use:

Private Sub cmboID_AfterUpdate()

Dim ClientName As String

ClientName = Me.cmbobox 'You would have to put the name of your combo box control name here.

DoCmd.OpenForm "frm_MyForm", , , "[Name] = '" & ClientName & "'" 'you would have to put the correct field name between the [] and put the correct name of your form.

End Sub

 

[lewie]

Thanks for the input I am trying to stay away from code for now so I used a macro. I have a form called menu with a button(btnopenform) to bring up the form and also have 2 combo boxes on the form to make selections( one is vendor and one is typetape.) I made a macro to open the form and in the where condition I placed [Customers]![vendor]=[Forms]![menu]![btnvendor] so the value you place in the cbo btnvendor = the record field vendor. Only it doen't work. If I run the macro it asks for vendor and if I put it in it works. Any ideas.

 

[ordendelfai]

I did a test and it worked ok for me.

What is the name of the combo box? Is it btnvendor? Make sure the combo box isn't the same name as the actual field (that has caused me problems in the past). I would name it "cmbo_btnvendor".

Then try:

Try [Vendor]=[Forms]![Menu]![cmbo_btnvendor]

If that doesn't work:

Double check the record source of the new form that is going to open when you click the button and make sure it has no criteria on it's source that would pop up a parameter query like it is doing now.

Make absolutely sure that whatever the combo box is bound to is the exact same field you are trying to use the where clause against in the underlying table that the new form would have it's criteria against.

Lastly, double check that your references are intact. I get to it by pressing CTRL + G, then going to menu and clicking Tools -> References. If you see anything checked that says "Missing: Name of Reference", then you need to uncheck it, go find it again and recheck it.

I strongly suggest using VBA, macros are slow, and get cumbersome when you have a lot. The common aspects of VBA are actually easier then macro's, it just doesn't seem that way at first. Try using my code if you can, it is basically cut and paste, and just change the names of field names to your names.

Let me know if this gets you anywhere.

 

[lewie]

I tried your code I put it in the click event of the button that opens the form. It won't select the record. I get no results. Is there any way to print the values of both sides of the equation so i can see which isn't what i expected.

 

[ordendelfai]

Put this in the after update of your drop down box (or the on click of your button).


Dim ClientID As Integer

ClientID = Me.cmbo_btnvendor 'You would have to put the name of your combo box control name here.

MsgBox "ClientID is " & ClientID & ""


A msgbox should pop up and show you what value is being stored into ClientID. This is assuming the value stored in the combo box is numerical and not Text. If it is text, use:

Dim ClientID As String

ClientID = Me.cmbo_btnvendor 'You would have to put the name of your combo box control name here.

MsgBox "ClientID is '" & ClientID & "'"

Hope this helps, anyone else have any ideas for him??

 

[mgmslate]

Create an unbound combo box on the form you want the data filled in and in the After Update event put in the following

' Find the record that matches the control.
If CboClient <> &quot;&quot; Then
Me.RecordsetClone.FindFirst &quot; [Clientid] = &quot; & Me![CboClient] '
Me.Bookmark = Me.RecordsetClone.Bookmark
CboClient = &quot;&quot;
End If

Mike

 

[lewie]

I have the customer table with a field vendor.
I made a form which uses the table.
I made a form on which I made a combo box named cmbvendor which displays the vendor field.
I placed a button on the form and in click i placed a macro
which opens customer form and in the where field I built an expression Customers![vendor]=[forms]![openform]![cmbvendor]
It displays an empty form when I select a vendor. This is just a test db with 2 records so i can figure out how to do it. I could send it to you or you could send one to me that works. I don't whats up it sems it would be a simple thing.
Thanks Lewie

 

[lewie]

Thanks for bearing with me. It finally works. I have a table, a menu form with a combo box, and a button running a macro and a form with the data. I was creating a combo box using the wizard and it was automatically sticking in the Primary key no matter what I did so of course the values didn't match. Unbelieveable. I'm glad.
Thanks again.

 

[ordendelfai]

I hate those wizards.....need more dragons to smite them