Subform field based on main form

[aw23]

I have a form with a subform. The form loads with a client based on user selection. When the subform opens I want some data based on the id of the company in the parent form. I can't bind it to the form because it is bound to a table wher I want the live data to go.
I am using the following code:

rs.Open "SELECT Customers.Customers_ID, Customers.Company_ID, Customers.C_FName, Customers.C_LName FROM customers WHERE Customers.Company_ID=" & Me!tmpCompany_ID.Value, Me.getConnection, adOpenStatic
While Not rs.EOF
    strContacts = strContacts & rs("c_fname") & " " & rs("c_lname") & "; "
rs.MoveNext
Wend
Forms("company")("futureactions")!cbContact.RowSource = Left(strContacts, Len(strContacts) - 2)
rs.Close
Set rs = Nothing
DoCmd.OpenForm "futureactions", , , , , acDialog

I don't get an error but the combobx is blank.
What am I doing wrong?
Thanks

 

[aw23]

I would put it into the subform but I can't figure out where to put it. The form is not visible and when the user exits a different subform it opens. So the code is currently on_exit of a different form.
I tried the following: activate,load,open,got focus. I put a msgbox in each one and none of them went when the form opened.
Any ideas where I can put it?

 

[PHV]

Have you tried simply this (with corrected RowSourceProperty) ?
Forms("company")("futureactions")!cbContact.RowSource = "SELECT Customers.Customers_ID, Customers.Company_ID, Customers.C_FName, Customers.C_LName FROM customers WHERE Customers.Company_ID=" & Me!tmpCompany_ID.Value

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[aw23]

That worked, it's populating except that I need to concatinate two of the fields, first name and last name. I set row source type to value list not table, query. I have done this before and it worked fine! How can I do it?
Thanks

 

[aw23]

Now it's not working at all anymore!
OK, I am really stuck here. I hope it's ok if I post the whole situation and maybe someone can help me out. Apparantly I am doing something very wrong. I have a main form which is the frontend to a database with 30,000 clients. The user selects one client from a combobox and main form loads with all the data. There is a subform on this main form called 'Action'. When the user contacts the client he fills out the action subform with all data about the action he has just completed which is all stored in the actions database. All this works great. I am working on the next part which I thought would be real simple but I am having a really hard time.
When the user leaves the action form a new form should pop up, the future action form (this part I have done). Basically the user needs to fill out his next planned action in the future. I will then set a reminder to remind him on that date. So basically it just needs to create a new action in the actions table. SO I created a bound form. It is boud to the actions table so that all actions can go directly into the table.
Now, all my code for opening the subform is in the main for on action-exit, for reasons I explained in my earlier post. I am having the following problems.
1.I have a txtbox for the date. It is formatted properly however I want the default value to be date() I set that as the default value and it doesn't show up.
2. I am having the same problem with the time txtbox.
3. The problem I explained initially. I want all the customers for that company to populate the combobox so that the user can select one (I have another table with all the customers for each company), it is not populating.
4. In the main form when I open the subform I coded to create a new record in the actions table (where I want all the bound controls to put their data) and it is not opening a new record, it did once and I have been testing it countless times.
5. I filled out the date and the information box, etc once to test. It did not go into the database but evertime I opne the form the information is still there.

I hope this makes sense.
Thanks

 

[aw23]

OK, I just solved part of the problem. Apparantly it was standing on a record and it was loading all that data. Now the date and time work. I have also unbound it. I think this is my main question.
How do I do the following:
Bind the form to the action table, when form opens, open a new record and all the bound fields pointing to that record. I still have more issues, like the customers but I want to start with that.
Thanks

 

[ProgramError]

Try...

put the 'action table name' in the recordsource preference of the form
and
in the OnLoad() preference

DoCmd.GoToRecord , "", acNewRec

cant remeber if this need to be i VBA or fyou can put it directly in the preference.

IM

 

[aw23]

Thanks so much.
I actually solved the problem. I changed the subform so that now it is no longer a subform but a form and I added docmd.gotorecord. From there is was simple.