change the combobox values based on a textbox (1 Viewer)

[s_samira_21]

I have a Form that contains a text box named "txt_OrgID" that shows the Organization ID, and a combo box named "cbx_OrgNam" that its list comes from "Organizations" table.
what I want is, after loading the form, if "txt_LstNam" is empty, the combo box shows the list that I set for it. But when "txt_LstNam" is not empty, the combo box, just like a text box, shows the organization Name that its ID is in "txt_OrgID"

 

[JamesMcS]

You'd have to set the row source for the combo box in VBA in the afterupdate property of the text box. So:

If txt_LastNam is null then comboname.rowsource = whatever

Else

comboname.rowsource = SQL statement with where clause to filter based on orgid

Endif

 

[s_samira_21]

it shows an error for this line:

cbx_OrgNam.rowsource = SQL statemenet with where clause to filter based on orgid

it says expected: end of statment

(I Don't know any thing about vba)

 

[JamesMcS]

OK - where I've put "SQL Statement with where clause..." I meant actually put your SQL statement in... Example:

cbx_OrgName.rowSource = "SELECT * From Orgnames WHERE [Orgnames].[OrgID]=" & txt_OrgID & ";"

What you need to do is have cbx_OrgName's row source as "table/query", create a query that shows everything you want to see in the combo box (including OrgID), and copy that SQL into your VBA as above. What I've done in the code above is to substitue an actual value for OrgID for the contents of the text box control txt_OrgID.

Similarly, don't put "whatever" as the SQL for the first part of the IF statement....

 

[s_samira_21]

I think it is not the good way to do my job!!!!
let me tell more about it...
Organization_Details form shows the details of the organization that I chose in Organization_list Split form. In Organization_Details I have a sub form Named Projects that shows some important details of the projects that the organization is doing. under the subform I have a command button named btn_AddPro that opens Project_Details form with empty Objects, to add new Project for that organization.
what I want is, when I open Project_Details by using the btn_AddPro button, the cbx_OrgNam combo box automatically choose the name of the organization that I am addin project for that.

 

[JamesMcS]

OK. First thing I can see there is that you're risking the user choosing the wrong organisation by giving them the option to change it when you add a project. But anyway...

What you need to do is have the Add Project form go to a new record when it opens, and just take the Org ID from the main form and stick it in the relevant field in the table your Add Projects form is based on. In the Add Projects form's On Open event, put this:

docmd.gotorecord , , acnewrec

This will make the form go to a new record. On the next line, put

[Org_ID] = forms![Organization_Details].[Org_ID]

This will take the Org_ID from the Organization_Details form and put it in the Org_ID field of your Add Projects form. The user will then enter project details. Put a "Save Record" button at the bottom of the add project form. In the on click event of that, put

forms![Organization_Details].form![Subform Control Name here].requery
docmd.close acForm, "Add Projects" <-Assuming that's the name of the Add Projects form

Job done.

 

[s_samira_21]

ok it works thank you!
but a new problem occurred!!!!!
Add project form (Its name is Project_details),will open in 2 conditions:
1) to add new project that you helped me to fix it
2) to edit existing projects
the problem is that if i put this line:
docmd.gotorecord , , acnewrec

in open form event, when I want to use the form for editing, It will go to the new record not the record that I want to edit!!!
to solve this problem I deleted this line and instead of that in on click event of the add project button, I used this:

DoCmd.OpenForm "Person_details", acNormal, , , acFormAdd, acDialog

but in this way the code you told me doesn't work and show this error message:

run-time error '-2147352567 (80020009)':
you cant assign a value to this object. (OrgID in add project form)

now what should I do to solve the problem

 

[JamesMcS]

Ok - in the place of "docmd.openform "Project_details",,acnewrec" in your button, put

docmd.openform "Project Details"
docmd.gotorecord acdataform,"Project Details", acnewrec

and take the gotorecord out of Project_detail's open form event.

 

[s_samira_21]

when the Project_Details in open by clicking on details field of each record, the correct details of the record is shown
but when the Project_Details form is closed by clicking on details of each record the new record is shown !!!!