Trying to return data to a combo box field from a different form

G

Guest

Hi, I am very new to access, so please bear with me. I have an order entry
form with a combo box for customer ID. It displays the company name, but it
is the custID that is bound to the table. What I want to do, is double click
on this field to open up the customer maintenance form, so that the company
info can be modified (or new account added) and then return to the order
entry form with the new information. It is this last piece that I am having
a problem with.

On the double click event of the combo box, I open the customer maintenance
form .

gstrCallingForm = Me.Name
gstrFieldName = "cboDlvCustID"

stDocName = "frmCustomerMaintenance"

stLinkCriteria = "[CustID]=" & Me![cboDlvCustID]
DoCmd.OpenForm stDocName, , , stLinkCriteria, , acDialog

On the close event of the customer maintenance form I have the following

If mstrCallingForm = "frmOrderHeader" Then
If mstrFieldName = "cboPUCustID" Then
Forms!frmOrderHeader.cboPUCustID.SetFocus
Forms!frmOrderHeader.cboPUCustID.Text = [Company]
End If
If mstrFieldName = "cboDlvCustID" Then
Forms!frmOrderHeader.cboDlvCustID.SetFocus
Forms!frmOrderHeader!cboDlvCustID.Text = [Company]

End If

This seems to work fine as long as I don’t have multiple accounts with the
same Company (which is the name). I really want to use CustID but when I
tried that instead of company I got a not in list error because it was
putting the account number in the combo box which displays the name.

I tried cboDLVCustID.ItemData(0) = [Custid] but I got errors on this too.

There must be a way to do this but I can’t seem to get the right keywords etc.

Any help would be truly appreciated.

Thanks
 
G

Guest

try

Forms!frmOrderHeader!cboDlvCustID = [Company]

This will change the value of the combobox (and does not require setting the
focus).

HTH
 
G

Guest

Sergey, thank you for your response. That does change the combo box, but
that is the problem, sort of. Company is the name, not the unique id. So if
I have more than 1 company with the same name, it returns me the first one in
the list - not necessarily the one I had open in customer maintenance. I'm
sure I could get around this by using the custid in the combo box and just
displaying the name as a textbox but I was trying to conserve screen space
and the users don't really care about the ID #. Thanks anyway.
--
Beth


Sergey Poberezovskiy said:
try

Forms!frmOrderHeader!cboDlvCustID = [Company]

This will change the value of the combobox (and does not require setting the
focus).

HTH
Bethh said:
Hi, I am very new to access, so please bear with me. I have an order entry
form with a combo box for customer ID. It displays the company name, but it
is the custID that is bound to the table. What I want to do, is double click
on this field to open up the customer maintenance form, so that the company
info can be modified (or new account added) and then return to the order
entry form with the new information. It is this last piece that I am having
a problem with.

On the double click event of the combo box, I open the customer maintenance
form .

gstrCallingForm = Me.Name
gstrFieldName = "cboDlvCustID"

stDocName = "frmCustomerMaintenance"

stLinkCriteria = "[CustID]=" & Me![cboDlvCustID]
DoCmd.OpenForm stDocName, , , stLinkCriteria, , acDialog

On the close event of the customer maintenance form I have the following

If mstrCallingForm = "frmOrderHeader" Then
If mstrFieldName = "cboPUCustID" Then
Forms!frmOrderHeader.cboPUCustID.SetFocus
Forms!frmOrderHeader.cboPUCustID.Text = [Company]
End If
If mstrFieldName = "cboDlvCustID" Then
Forms!frmOrderHeader.cboDlvCustID.SetFocus
Forms!frmOrderHeader!cboDlvCustID.Text = [Company]

End If

This seems to work fine as long as I don’t have multiple accounts with the
same Company (which is the name). I really want to use CustID but when I
tried that instead of company I got a not in list error because it was
putting the account number in the combo box which displays the name.

I tried cboDLVCustID.ItemData(0) = [Custid] but I got errors on this too.

There must be a way to do this but I can’t seem to get the right keywords etc.

Any help would be truly appreciated.

Thanks
Click to expand...
Click to expand...
 
G

Guest

Bethh,

My bad - As long as you have your combobox's Bound to CompanyID and set the
combo value to an ID (not a name) you should be fine:

Forms!frmOrderHeader!cboDlvCustID = [Custid]

HTH
Bethh said:
Sergey, thank you for your response. That does change the combo box, but
that is the problem, sort of. Company is the name, not the unique id. So if
I have more than 1 company with the same name, it returns me the first one in
the list - not necessarily the one I had open in customer maintenance. I'm
sure I could get around this by using the custid in the combo box and just
displaying the name as a textbox but I was trying to conserve screen space
and the users don't really care about the ID #. Thanks anyway.
--
Beth


Sergey Poberezovskiy said:
try

Forms!frmOrderHeader!cboDlvCustID = [Company]

This will change the value of the combobox (and does not require setting the
focus).

HTH
Bethh said:
Hi, I am very new to access, so please bear with me. I have an order entry
form with a combo box for customer ID. It displays the company name, but it
is the custID that is bound to the table. What I want to do, is double click
on this field to open up the customer maintenance form, so that the company
info can be modified (or new account added) and then return to the order
entry form with the new information. It is this last piece that I am having
a problem with.

On the double click event of the combo box, I open the customer maintenance
form .

gstrCallingForm = Me.Name
gstrFieldName = "cboDlvCustID"

stDocName = "frmCustomerMaintenance"

stLinkCriteria = "[CustID]=" & Me![cboDlvCustID]
DoCmd.OpenForm stDocName, , , stLinkCriteria, , acDialog

On the close event of the customer maintenance form I have the following

If mstrCallingForm = "frmOrderHeader" Then
If mstrFieldName = "cboPUCustID" Then
Forms!frmOrderHeader.cboPUCustID.SetFocus
Forms!frmOrderHeader.cboPUCustID.Text = [Company]
End If
If mstrFieldName = "cboDlvCustID" Then
Forms!frmOrderHeader.cboDlvCustID.SetFocus
Forms!frmOrderHeader!cboDlvCustID.Text = [Company]

End If

This seems to work fine as long as I don’t have multiple accounts with the
same Company (which is the name). I really want to use CustID but when I
tried that instead of company I got a not in list error because it was
putting the account number in the combo box which displays the name.

I tried cboDLVCustID.ItemData(0) = [Custid] but I got errors on this too.

There must be a way to do this but I can’t seem to get the right keywords etc.

Any help would be truly appreciated.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Yes, that would work, but I was trying to avoid dipalying both the name and
the ID. I guess I will just have to do it that way.
Thanks for your help.
--
Beth


Sergey Poberezovskiy said:
Bethh,

My bad - As long as you have your combobox's Bound to CompanyID and set the
combo value to an ID (not a name) you should be fine:

Forms!frmOrderHeader!cboDlvCustID = [Custid]

HTH
Bethh said:
Sergey, thank you for your response. That does change the combo box, but
that is the problem, sort of. Company is the name, not the unique id. So if
I have more than 1 company with the same name, it returns me the first one in
the list - not necessarily the one I had open in customer maintenance. I'm
sure I could get around this by using the custid in the combo box and just
displaying the name as a textbox but I was trying to conserve screen space
and the users don't really care about the ID #. Thanks anyway.
--
Beth


Sergey Poberezovskiy said:
try

Forms!frmOrderHeader!cboDlvCustID = [Company]

This will change the value of the combobox (and does not require setting the
focus).

HTH
:

Hi, I am very new to access, so please bear with me. I have an order entry
form with a combo box for customer ID. It displays the company name, but it
is the custID that is bound to the table. What I want to do, is double click
on this field to open up the customer maintenance form, so that the company
info can be modified (or new account added) and then return to the order
entry form with the new information. It is this last piece that I am having
a problem with.

On the double click event of the combo box, I open the customer maintenance
form .

gstrCallingForm = Me.Name
gstrFieldName = "cboDlvCustID"

stDocName = "frmCustomerMaintenance"

stLinkCriteria = "[CustID]=" & Me![cboDlvCustID]
DoCmd.OpenForm stDocName, , , stLinkCriteria, , acDialog

On the close event of the customer maintenance form I have the following

If mstrCallingForm = "frmOrderHeader" Then
If mstrFieldName = "cboPUCustID" Then
Forms!frmOrderHeader.cboPUCustID.SetFocus
Forms!frmOrderHeader.cboPUCustID.Text = [Company]
End If
If mstrFieldName = "cboDlvCustID" Then
Forms!frmOrderHeader.cboDlvCustID.SetFocus
Forms!frmOrderHeader!cboDlvCustID.Text = [Company]

End If

This seems to work fine as long as I don’t have multiple accounts with the
same Company (which is the name). I really want to use CustID but when I
tried that instead of company I got a not in list error because it was
putting the account number in the combo box which displays the name.

I tried cboDLVCustID.ItemData(0) = [Custid] but I got errors on this too.

There must be a way to do this but I can’t seem to get the right keywords etc.

Any help would be truly appreciated.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Beth,

You just have to change the ColumnWidths property so that it hides the ID
column -something like:
myBox.ColumnWidths = "0cm;2cm;"

This will set the width of the first (ID) column to zero, and the last to
say, 2 cm.

Bethh said:
Yes, that would work, but I was trying to avoid dipalying both the name and
the ID. I guess I will just have to do it that way.
Thanks for your help.
--
Beth


Sergey Poberezovskiy said:
Bethh,

My bad - As long as you have your combobox's Bound to CompanyID and set the
combo value to an ID (not a name) you should be fine:

Forms!frmOrderHeader!cboDlvCustID = [Custid]

HTH
Bethh said:
Sergey, thank you for your response. That does change the combo box, but
that is the problem, sort of. Company is the name, not the unique id. So if
I have more than 1 company with the same name, it returns me the first one in
the list - not necessarily the one I had open in customer maintenance. I'm
sure I could get around this by using the custid in the combo box and just
displaying the name as a textbox but I was trying to conserve screen space
and the users don't really care about the ID #. Thanks anyway.
--
Beth


:

try

Forms!frmOrderHeader!cboDlvCustID = [Company]

This will change the value of the combobox (and does not require setting the
focus).

HTH
:

Hi, I am very new to access, so please bear with me. I have an order entry
form with a combo box for customer ID. It displays the company name, but it
is the custID that is bound to the table. What I want to do, is double click
on this field to open up the customer maintenance form, so that the company
info can be modified (or new account added) and then return to the order
entry form with the new information. It is this last piece that I am having
a problem with.

On the double click event of the combo box, I open the customer maintenance
form .

gstrCallingForm = Me.Name
gstrFieldName = "cboDlvCustID"

stDocName = "frmCustomerMaintenance"

stLinkCriteria = "[CustID]=" & Me![cboDlvCustID]
DoCmd.OpenForm stDocName, , , stLinkCriteria, , acDialog

On the close event of the customer maintenance form I have the following

If mstrCallingForm = "frmOrderHeader" Then
If mstrFieldName = "cboPUCustID" Then
Forms!frmOrderHeader.cboPUCustID.SetFocus
Forms!frmOrderHeader.cboPUCustID.Text = [Company]
End If
If mstrFieldName = "cboDlvCustID" Then
Forms!frmOrderHeader.cboDlvCustID.SetFocus
Forms!frmOrderHeader!cboDlvCustID.Text = [Company]

End If

This seems to work fine as long as I don’t have multiple accounts with the
same Company (which is the name). I really want to use CustID but when I
tried that instead of company I got a not in list error because it was
putting the account number in the combo box which displays the name.

I tried cboDLVCustID.ItemData(0) = [Custid] but I got errors on this too.

There must be a way to do this but I can’t seem to get the right keywords etc.

Any help would be truly appreciated.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Sergey,
That's the way I have it set up now. So that it displays company but custid
is the bound field (not displayed) but if I set the combo box = custid I get
a not in list error. if I set it = company, it works. That's my problem.
--
Beth


Sergey Poberezovskiy said:
Beth,

You just have to change the ColumnWidths property so that it hides the ID
column -something like:
myBox.ColumnWidths = "0cm;2cm;"

This will set the width of the first (ID) column to zero, and the last to
say, 2 cm.

Bethh said:
Yes, that would work, but I was trying to avoid dipalying both the name and
the ID. I guess I will just have to do it that way.
Thanks for your help.
--
Beth


Sergey Poberezovskiy said:
Bethh,

My bad - As long as you have your combobox's Bound to CompanyID and set the
combo value to an ID (not a name) you should be fine:

Forms!frmOrderHeader!cboDlvCustID = [Custid]

HTH
:

Sergey, thank you for your response. That does change the combo box, but
that is the problem, sort of. Company is the name, not the unique id. So if
I have more than 1 company with the same name, it returns me the first one in
the list - not necessarily the one I had open in customer maintenance. I'm
sure I could get around this by using the custid in the combo box and just
displaying the name as a textbox but I was trying to conserve screen space
and the users don't really care about the ID #. Thanks anyway.
--
Beth


:

try

Forms!frmOrderHeader!cboDlvCustID = [Company]

This will change the value of the combobox (and does not require setting the
focus).

HTH
:

Hi, I am very new to access, so please bear with me. I have an order entry
form with a combo box for customer ID. It displays the company name, but it
is the custID that is bound to the table. What I want to do, is double click
on this field to open up the customer maintenance form, so that the company
info can be modified (or new account added) and then return to the order
entry form with the new information. It is this last piece that I am having
a problem with.

On the double click event of the combo box, I open the customer maintenance
form .

gstrCallingForm = Me.Name
gstrFieldName = "cboDlvCustID"

stDocName = "frmCustomerMaintenance"

stLinkCriteria = "[CustID]=" & Me![cboDlvCustID]
DoCmd.OpenForm stDocName, , , stLinkCriteria, , acDialog

On the close event of the customer maintenance form I have the following

If mstrCallingForm = "frmOrderHeader" Then
If mstrFieldName = "cboPUCustID" Then
Forms!frmOrderHeader.cboPUCustID.SetFocus
Forms!frmOrderHeader.cboPUCustID.Text = [Company]
End If
If mstrFieldName = "cboDlvCustID" Then
Forms!frmOrderHeader.cboDlvCustID.SetFocus
Forms!frmOrderHeader!cboDlvCustID.Text = [Company]

End If

This seems to work fine as long as I don’t have multiple accounts with the
same Company (which is the name). I really want to use CustID but when I
tried that instead of company I got a not in list error because it was
putting the account number in the combo box which displays the name.

I tried cboDLVCustID.ItemData(0) = [Custid] but I got errors on this too.

There must be a way to do this but I can’t seem to get the right keywords etc.

Any help would be truly appreciated.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Beth,

Please ensure the following for your combobox:
- RowSource returns ID as one of the fields
- BoundColumn is set to the ID field ordinal (starting from 1 - not 0)
- ControlSource is not set or set to ID field

then if you set the combobox (not it's Text) to an ID it is bound to work -
it does not matter whether the id is visible or not.

HTH

Bethh said:
Sergey,
That's the way I have it set up now. So that it displays company but custid
is the bound field (not displayed) but if I set the combo box = custid I get
a not in list error. if I set it = company, it works. That's my problem.
--
Beth


Sergey Poberezovskiy said:
Beth,

You just have to change the ColumnWidths property so that it hides the ID
column -something like:
myBox.ColumnWidths = "0cm;2cm;"

This will set the width of the first (ID) column to zero, and the last to
say, 2 cm.

Bethh said:
Yes, that would work, but I was trying to avoid dipalying both the name and
the ID. I guess I will just have to do it that way.
Thanks for your help.
--
Beth


:

Bethh,

My bad - As long as you have your combobox's Bound to CompanyID and set the
combo value to an ID (not a name) you should be fine:

Forms!frmOrderHeader!cboDlvCustID = [Custid]

HTH
:

Sergey, thank you for your response. That does change the combo box, but
that is the problem, sort of. Company is the name, not the unique id. So if
I have more than 1 company with the same name, it returns me the first one in
the list - not necessarily the one I had open in customer maintenance. I'm
sure I could get around this by using the custid in the combo box and just
displaying the name as a textbox but I was trying to conserve screen space
and the users don't really care about the ID #. Thanks anyway.
--
Beth


:

try

Forms!frmOrderHeader!cboDlvCustID = [Company]

This will change the value of the combobox (and does not require setting the
focus).

HTH
:

Hi, I am very new to access, so please bear with me. I have an order entry
form with a combo box for customer ID. It displays the company name, but it
is the custID that is bound to the table. What I want to do, is double click
on this field to open up the customer maintenance form, so that the company
info can be modified (or new account added) and then return to the order
entry form with the new information. It is this last piece that I am having
a problem with.

On the double click event of the combo box, I open the customer maintenance
form .

gstrCallingForm = Me.Name
gstrFieldName = "cboDlvCustID"

stDocName = "frmCustomerMaintenance"

stLinkCriteria = "[CustID]=" & Me![cboDlvCustID]
DoCmd.OpenForm stDocName, , , stLinkCriteria, , acDialog

On the close event of the customer maintenance form I have the following

If mstrCallingForm = "frmOrderHeader" Then
If mstrFieldName = "cboPUCustID" Then
Forms!frmOrderHeader.cboPUCustID.SetFocus
Forms!frmOrderHeader.cboPUCustID.Text = [Company]
End If
If mstrFieldName = "cboDlvCustID" Then
Forms!frmOrderHeader.cboDlvCustID.SetFocus
Forms!frmOrderHeader!cboDlvCustID.Text = [Company]

End If

This seems to work fine as long as I don’t have multiple accounts with the
same Company (which is the name). I really want to use CustID but when I
tried that instead of company I got a not in list error because it was
putting the account number in the combo box which displays the name.

I tried cboDLVCustID.ItemData(0) = [Custid] but I got errors on this too.

There must be a way to do this but I can’t seem to get the right keywords etc.

Any help would be truly appreciated.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Sergey,
Thank you so much for all your help and patience. I believe, I had it set
up like that but I have changed it so many times it's hard to know for sure.
Anyway, I got it working now although I'm not exactly sure what I changed to
make it work. Thanks so much for sticking with me.
--
Beth


Sergey Poberezovskiy said:
Beth,

Please ensure the following for your combobox:
- RowSource returns ID as one of the fields
- BoundColumn is set to the ID field ordinal (starting from 1 - not 0)
- ControlSource is not set or set to ID field

then if you set the combobox (not it's Text) to an ID it is bound to work -
it does not matter whether the id is visible or not.

HTH

Bethh said:
Sergey,
That's the way I have it set up now. So that it displays company but custid
is the bound field (not displayed) but if I set the combo box = custid I get
a not in list error. if I set it = company, it works. That's my problem.
--
Beth


Sergey Poberezovskiy said:
Beth,

You just have to change the ColumnWidths property so that it hides the ID
column -something like:
myBox.ColumnWidths = "0cm;2cm;"

This will set the width of the first (ID) column to zero, and the last to
say, 2 cm.

:

Yes, that would work, but I was trying to avoid dipalying both the name and
the ID. I guess I will just have to do it that way.
Thanks for your help.
--
Beth


:

Bethh,

My bad - As long as you have your combobox's Bound to CompanyID and set the
combo value to an ID (not a name) you should be fine:

Forms!frmOrderHeader!cboDlvCustID = [Custid]

HTH
:

Sergey, thank you for your response. That does change the combo box, but
that is the problem, sort of. Company is the name, not the unique id. So if
I have more than 1 company with the same name, it returns me the first one in
the list - not necessarily the one I had open in customer maintenance. I'm
sure I could get around this by using the custid in the combo box and just
displaying the name as a textbox but I was trying to conserve screen space
and the users don't really care about the ID #. Thanks anyway.
--
Beth


:

try

Forms!frmOrderHeader!cboDlvCustID = [Company]

This will change the value of the combobox (and does not require setting the
focus).

HTH
:

Hi, I am very new to access, so please bear with me. I have an order entry
form with a combo box for customer ID. It displays the company name, but it
is the custID that is bound to the table. What I want to do, is double click
on this field to open up the customer maintenance form, so that the company
info can be modified (or new account added) and then return to the order
entry form with the new information. It is this last piece that I am having
a problem with.

On the double click event of the combo box, I open the customer maintenance
form .

gstrCallingForm = Me.Name
gstrFieldName = "cboDlvCustID"

stDocName = "frmCustomerMaintenance"

stLinkCriteria = "[CustID]=" & Me![cboDlvCustID]
DoCmd.OpenForm stDocName, , , stLinkCriteria, , acDialog

On the close event of the customer maintenance form I have the following

If mstrCallingForm = "frmOrderHeader" Then
If mstrFieldName = "cboPUCustID" Then
Forms!frmOrderHeader.cboPUCustID.SetFocus
Forms!frmOrderHeader.cboPUCustID.Text = [Company]
End If
If mstrFieldName = "cboDlvCustID" Then
Forms!frmOrderHeader.cboDlvCustID.SetFocus
Forms!frmOrderHeader!cboDlvCustID.Text = [Company]

End If

This seems to work fine as long as I don’t have multiple accounts with the
same Company (which is the name). I really want to use CustID but when I
tried that instead of company I got a not in list error because it was
putting the account number in the combo box which displays the name.

I tried cboDLVCustID.ItemData(0) = [Custid] but I got errors on this too.

There must be a way to do this but I can’t seem to get the right keywords etc.

Any help would be truly appreciated.

Thanks
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Combo On Not In List - am I asking too much? 2
Link subform to combo box on form 3
linking data on a form 1
Help Needed!! - Issue with an unbound combo box 6
Code modification to select a unique record from a list box 1
Conditional Formatting in Combo Box (ACC2007) 2
Launching a Form to Another Form to Create a New Record 11
Open different form based on combo box selection 2

Top