Question regarding import of combo box to text box

[accessuser2023]

Hi All,

I am by no means a professional programmer, and I am also new to this forum. However, I have self-taught myself access and a lot of VB that goes with it. I enjoy using it for my work, and can't yet migrate to anything else.



I have one form with a text box that has the name 'Job Titles'. On the other form I have the same combo box field 'Job Titles'. When the user clicks import on the form with the text box, it will not import the value to the combo box. Is there code or a way to execute this? I can import textbox to textbox just fine. However combobox value to a text box is not working for me.

Thanks in advance! I am sure it is something simple.

[Welshgasman]

I am not sure which direction you want the value to go to?, as you appear to contradict yourself?

import the value to the combo box

However combobox value to a text box is not working

[ranman256]

use the full path

forms!form1!txtBox = forms!form2!cboBox

[Gicu]

How do you "import" on the form with the textbox? What is the row source of the combo box? You might need to requery the row source of the combo to get the new value to show up in the drop-down list:

Me.cboJobTitle.Requery

Cheers,

[accessuser2023]

Sorry let me clarify. I want the value to be passed from the text box on the one form to a combo box on the other. I hope this makes sense.

@ranman256 - I am going to try your suggestion. Thank you!

[Welshgasman]

Sorry let me clarify. I want the value to be passed from the text box on the one form to a combo box on the other. I hope this makes sense.

@ranman256 - I am going to try your suggestion. Thank you!

Well you are going to have to swap that suggestion around.

[accessuser2023]

Forms![form1]![JobTitle] = Forms![form2]![JOBTITLE]

This is what I am using in a command button on click event.

The rowsource of the combobox gets the values from a table called 'JobTitles' I am thinking this is where the 'issue' lies perhaps

[Welshgasman]

Forms![form1]![JobTitle] = Forms![form2]![JOBTITLE]

This is what I am using in a command button on click event.

Well you said the textbox was on form1 and the combo on form2 and you want the value from form1 to go to form 2?

See anything wrong with that statement?

[Gicu]

As asked earlier, what is the row source of the combo; if the text box shows a descriptive job title (Manager) but the combo is bound to a numeric JobTitleID (i.e. 1 being the unique ID corresponding to the "Manager" record in a tblJobTitle lookup table) passing it directly like that won't work. This assumes the combo has a two column row source with the first one (ID) hidden. You would need to use a dLookup:

Forms!frmWithCombo!JobTitle=dLookup("JobTitleID"," tblJobTitle","JobTitle='" & Forms!frmWithTextBox!JobTitle & "'")

Cheers,

[accessuser2023]

@Gicu this was the issue and it worked. Thank you! The value has to already be in the job title table which is fine, but if it isn't, the field shows up blank. Would there be a way to easily code this to notify me or place the value in the combo box even if it is not in the table, so I can simply add it?

[Gicu]

Of course there is, in your click event you can test for the value using a dCount (or the return value of the dLookup):

If dCount("*"," tblJobTitle","JobTitle='" & Forms!frmWithTextBox!JobTitle & "'")=0 Then
     If Msgbox("The select job title does not yet exist in the Job Titels table. Do you want to add it?",vbquestion +vbYesNo,"Add newjob title")=vbNo then Exit Sub 'skip updating the combo
    'if you got here the user wants to add it
    'open a modal\dialog form bount to the tblJobTitles in DataAdd mode and pass it the new value directly or as the OpenArgs
   docmd.OpenForm "popAddJobTitle",acNormal,,,acFormAdd,acDialog,Me.JobTitle 'using OpenArgs - in the Load event of the modal form add Me.JobTitle=Me.OpenArgs
   Forms!popAddJobTitle!JobTitle=Me.JobTitle ' directly into the now open form - ;this line needs the previous one but without the OpenArgs
End If
'now that the modal form is closed and the new value added to the table go ahead and update the combo
Forms!frmWithCombo!JobTitle=dLookup("JobTitleID"," tblJobTitle","JobTitle='" & Me.JobTitle & "'")

Cheers,

[accessuser2023]

This was all very helpful. My issue is fixed. Thanks for all of the help!

[Gicu]

You're very welcome!
Cheers,