Possible to change control source?

[Guest]

I have a form that displays about fifteen fields via text boxes and combo
boxes.
One particular combo box auto-populates two text boxes by the text box's
control source set to: =[comboboxName].column(2) and
=[comboboxName].column(3) respectivly.
I'd like to have the ability to add a new value into the combo box that is
NOT already in the list. When I do add a new combobox value, I'd like to
temporarily change the control source of the following two text boxes to
allow input.
Is this possible or is there an easier way to accomplish this?

 

[Guest]

You can change the control source through code.

Me.MyTextBox.ControlSource = whatever

You will need to change them back after you update your table. That would
probably be in the form After Update event.

 

[Guest]

Thank you for your quick response. However I may have put the cart before
the horse.
I was thinking about using the NOT IN LIST event to trigger the change of
the control source. But apparnetly that event - or my understanding of that
event - will not support two simple lines of code.
Any suggestions on how to trigger the code you suggested.

You can change the control source through code.

Me.MyTextBox.ControlSource = whatever

You will need to change them back after you update your table. That would
probably be in the form After Update event.

I have a form that displays about fifteen fields via text boxes and combo
boxes.
One particular combo box auto-populates two text boxes by the text box's
control source set to: =[comboboxName].column(2) and
=[comboboxName].column(3) respectivly.
I'd like to have the ability to add a new value into the combo box that is
NOT already in the list. When I do add a new combobox value, I'd like to
temporarily change the control source of the following two text boxes to
allow input.
Is this possible or is there an easier way to accomplish this?

 

[Guest]

How are you doing your NotInList event? It works like any other event. If
you are trying to use an expression or a macro, it might be a problem, but if
you put it in the NotInList event using VBA it should work just fine.

Thank you for your quick response. However I may have put the cart before
the horse.
I was thinking about using the NOT IN LIST event to trigger the change of
the control source. But apparnetly that event - or my understanding of that
event - will not support two simple lines of code.
Any suggestions on how to trigger the code you suggested.

You can change the control source through code.

Me.MyTextBox.ControlSource = whatever

You will need to change them back after you update your table. That would
probably be in the form After Update event.

I have a form that displays about fifteen fields via text boxes and combo
boxes.
One particular combo box auto-populates two text boxes by the text box's
control source set to: =[comboboxName].column(2) and
=[comboboxName].column(3) respectivly.
I'd like to have the ability to add a new value into the combo box that is
NOT already in the list. When I do add a new combobox value, I'd like to
temporarily change the control source of the following two text boxes to
allow input.
Is this possible or is there an easier way to accomplish this?

 

[Guest]

Klatuu - Sorry to be such a rock, but I can't seem to get this to work.
I'm getting error messages or the MS ACCESS dialog telling me to select an
item from the list.
The sun is going down now and it time for me to leave today.
I'll attack it again tomorrow.
Please check this string again tomorrow.
Regards,
Daiuy

How are you doing your NotInList event? It works like any other event. If
you are trying to use an expression or a macro, it might be a problem, but if
you put it in the NotInList event using VBA it should work just fine.

Thank you for your quick response. However I may have put the cart before
the horse.
I was thinking about using the NOT IN LIST event to trigger the change of
the control source. But apparnetly that event - or my understanding of that
event - will not support two simple lines of code.
Any suggestions on how to trigger the code you suggested.

You can change the control source through code.

Me.MyTextBox.ControlSource = whatever

You will need to change them back after you update your table. That would
probably be in the form After Update event.

:

I have a form that displays about fifteen fields via text boxes and combo
boxes.
One particular combo box auto-populates two text boxes by the text box's
control source set to: =[comboboxName].column(2) and
=[comboboxName].column(3) respectivly.
I'd like to have the ability to add a new value into the combo box that is
NOT already in the list. When I do add a new combobox value, I'd like to
temporarily change the control source of the following two text boxes to
allow input.
Is this possible or is there an easier way to accomplish this?

 

[Guest]

Here is the code I've tried so far:

Private Sub CboRequestor_NotInList(NewData As String, Response As Integer)
Dim intAnswer As Integer
intAnswer = MsgBox("This appears to be a new Requestor. Add?
(Yes/No)", vbYesNo + vbQuestion)
If intAnswer = vbYes Then
DoCmd.OpenForm "New Requestor", , , TxtID =
Forms![frmAccessControl]![ID], acFormAdd, acDialog
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

The code above is not working. I've played with it and played with it, but
no success. It inputs the correct data in the correct fields but it in a new
record by itself.

I also tried:

Private Sub CboRequestor_NotInList(NewData As String, Response As Integer)
Response = acDataErrContinue
Me.Section.ControlSource = tblAccess_Control!Section
Me.Telephone.ControlSource = tblAccess_Control!Telephone
End Sub
I've played with this one as well but I keep getting an compile error:
Argument not Optional.

What I'd really like to happen is for the user to type in the name of the
requestor in the requestor's combobox. As he types, a name that is already
in the list will be displayed. The user can than TAB or ENTER to the next
textbox which is Section which is automatically filled by the control source
set to: =[cboRequestor].column(3) and TAB to the next text box which is
Telephone that has its control source set to: =[cboRequestor].column(4).
However, if the user types in a requestor who is not in the list, I'd like to
capture the rerquestor's name and allow the user to input the section and
phone number.

If I set the LIMIT TO LIST property for cboRequestor to NO, How do I get the
Me.Section.ControlSource = tblAccess_Control!Section to fire? And then
how to I set it back to column(3) after the edit.

If I set the Limit to List property to YES, I can see where I can have the
NOT IN LIST event to trigger the code however I don't have the right code.
Please help.

Klatuu - Sorry to be such a rock, but I can't seem to get this to work.
I'm getting error messages or the MS ACCESS dialog telling me to select an
item from the list.
The sun is going down now and it time for me to leave today.
I'll attack it again tomorrow.
Please check this string again tomorrow.
Regards,
Daiuy

How are you doing your NotInList event? It works like any other event. If
you are trying to use an expression or a macro, it might be a problem, but if
you put it in the NotInList event using VBA it should work just fine.

Thank you for your quick response. However I may have put the cart before
the horse.
I was thinking about using the NOT IN LIST event to trigger the change of
the control source. But apparnetly that event - or my understanding of that
event - will not support two simple lines of code.
Any suggestions on how to trigger the code you suggested.

:

You can change the control source through code.

Me.MyTextBox.ControlSource = whatever

You will need to change them back after you update your table. That would
probably be in the form After Update event.

:

I have a form that displays about fifteen fields via text boxes and combo
boxes.
One particular combo box auto-populates two text boxes by the text box's
control source set to: =[comboboxName].column(2) and
=[comboboxName].column(3) respectivly.
I'd like to have the ability to add a new value into the combo box that is
NOT already in the list. When I do add a new combobox value, I'd like to
temporarily change the control source of the following two text boxes to
allow input.
Is this possible or is there an easier way to accomplish this?

 

[Guest]

It will create a new record. Your acFormAdd argument opens the form to add
new records. This is from Access Help:

acFormAdd The user can add new records but can't edit existing records.

Also, to get the new requestor to show up in the list, you need to do a
requery.

Where are the section and phone entered? In the Requestor form? If so, can
you get them from there before you close the requestor form? Without doing
the code, my thought is that in whatever event in th requestor form closes
the requestor form, check to see if the form you are calling it form is open,
and if it is, update the contorls on the original form from there.

I hope I am making sense and helping here. Please post back if you have
more questions or need clarification.

Here is the code I've tried so far:

Private Sub CboRequestor_NotInList(NewData As String, Response As Integer)
Dim intAnswer As Integer
intAnswer = MsgBox("This appears to be a new Requestor. Add?
(Yes/No)", vbYesNo + vbQuestion)
If intAnswer = vbYes Then
DoCmd.OpenForm "New Requestor", , , TxtID =
Forms![frmAccessControl]![ID], acFormAdd, acDialog
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

The code above is not working. I've played with it and played with it, but
no success. It inputs the correct data in the correct fields but it in a new
record by itself.

I also tried:

Private Sub CboRequestor_NotInList(NewData As String, Response As Integer)
Response = acDataErrContinue
Me.Section.ControlSource = tblAccess_Control!Section
Me.Telephone.ControlSource = tblAccess_Control!Telephone
End Sub
I've played with this one as well but I keep getting an compile error:
Argument not Optional.

What I'd really like to happen is for the user to type in the name of the
requestor in the requestor's combobox. As he types, a name that is already
in the list will be displayed. The user can than TAB or ENTER to the next
textbox which is Section which is automatically filled by the control source
set to: =[cboRequestor].column(3) and TAB to the next text box which is
Telephone that has its control source set to: =[cboRequestor].column(4).
However, if the user types in a requestor who is not in the list, I'd like to
capture the rerquestor's name and allow the user to input the section and
phone number.

If I set the LIMIT TO LIST property for cboRequestor to NO, How do I get the
Me.Section.ControlSource = tblAccess_Control!Section to fire? And then
how to I set it back to column(3) after the edit.

If I set the Limit to List property to YES, I can see where I can have the
NOT IN LIST event to trigger the code however I don't have the right code.
Please help.

Klatuu - Sorry to be such a rock, but I can't seem to get this to work.
I'm getting error messages or the MS ACCESS dialog telling me to select an
item from the list.
The sun is going down now and it time for me to leave today.
I'll attack it again tomorrow.
Please check this string again tomorrow.
Regards,
Daiuy

How are you doing your NotInList event? It works like any other event. If
you are trying to use an expression or a macro, it might be a problem, but if
you put it in the NotInList event using VBA it should work just fine.

:

Thank you for your quick response. However I may have put the cart before
the horse.
I was thinking about using the NOT IN LIST event to trigger the change of
the control source. But apparnetly that event - or my understanding of that
event - will not support two simple lines of code.
Any suggestions on how to trigger the code you suggested.

:

You can change the control source through code.

Me.MyTextBox.ControlSource = whatever

You will need to change them back after you update your table. That would
probably be in the form After Update event.

:

I have a form that displays about fifteen fields via text boxes and combo
boxes.
One particular combo box auto-populates two text boxes by the text box's
control source set to: =[comboboxName].column(2) and
=[comboboxName].column(3) respectivly.
I'd like to have the ability to add a new value into the combo box that is
NOT already in the list. When I do add a new combobox value, I'd like to
temporarily change the control source of the following two text boxes to
allow input.
Is this possible or is there an easier way to accomplish this?

 

[Guest]

Perhaps my approach is all wrong.
I have a single table entitled tblAccess_Control.
The Prmary Key is an AutoNumber.
I have a form entitled frmAccessControl that is used for data entry.
I have another form entitled NewRequestor which contains four fields
(RequestorLast, RequestorFirst, Section, and Phone).
If the combobox cboRequestor LIMIT TO LIST property is set to YES and I use
the NOT IN LIST event to open the form NewRequestor as a pop-up from, is it
possible to enter the data in the four fields of the NewRequestor form and
have it inserted into the appropriate recordset in tblAccess_Control and then
have that data immediately displayed in frmAccessControl?
If you couldn't tell before now, my skill level is low but my ambition is
high.
Thanks again,
Daiuy

It will create a new record. Your acFormAdd argument opens the form to add
new records. This is from Access Help:

acFormAdd The user can add new records but can't edit existing records.

Also, to get the new requestor to show up in the list, you need to do a
requery.

Where are the section and phone entered? In the Requestor form? If so, can
you get them from there before you close the requestor form? Without doing
the code, my thought is that in whatever event in th requestor form closes
the requestor form, check to see if the form you are calling it form is open,
and if it is, update the contorls on the original form from there.

I hope I am making sense and helping here. Please post back if you have
more questions or need clarification.

Here is the code I've tried so far:

Private Sub CboRequestor_NotInList(NewData As String, Response As Integer)
Dim intAnswer As Integer
intAnswer = MsgBox("This appears to be a new Requestor. Add?
(Yes/No)", vbYesNo + vbQuestion)
If intAnswer = vbYes Then
DoCmd.OpenForm "New Requestor", , , TxtID =
Forms![frmAccessControl]![ID], acFormAdd, acDialog
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

The code above is not working. I've played with it and played with it, but
no success. It inputs the correct data in the correct fields but it in a new
record by itself.

I also tried:

Private Sub CboRequestor_NotInList(NewData As String, Response As Integer)
Response = acDataErrContinue
Me.Section.ControlSource = tblAccess_Control!Section
Me.Telephone.ControlSource = tblAccess_Control!Telephone
End Sub
I've played with this one as well but I keep getting an compile error:
Argument not Optional.

What I'd really like to happen is for the user to type in the name of the
requestor in the requestor's combobox. As he types, a name that is already
in the list will be displayed. The user can than TAB or ENTER to the next
textbox which is Section which is automatically filled by the control source
set to: =[cboRequestor].column(3) and TAB to the next text box which is
Telephone that has its control source set to: =[cboRequestor].column(4).
However, if the user types in a requestor who is not in the list, I'd like to
capture the rerquestor's name and allow the user to input the section and
phone number.

If I set the LIMIT TO LIST property for cboRequestor to NO, How do I get the
Me.Section.ControlSource = tblAccess_Control!Section to fire? And then
how to I set it back to column(3) after the edit.

If I set the Limit to List property to YES, I can see where I can have the
NOT IN LIST event to trigger the code however I don't have the right code.
Please help.

Klatuu - Sorry to be such a rock, but I can't seem to get this to work.
I'm getting error messages or the MS ACCESS dialog telling me to select an
item from the list.
The sun is going down now and it time for me to leave today.
I'll attack it again tomorrow.
Please check this string again tomorrow.
Regards,
Daiuy

:

How are you doing your NotInList event? It works like any other event. If
you are trying to use an expression or a macro, it might be a problem, but if
you put it in the NotInList event using VBA it should work just fine.

:

Thank you for your quick response. However I may have put the cart before
the horse.
I was thinking about using the NOT IN LIST event to trigger the change of
the control source. But apparnetly that event - or my understanding of that
event - will not support two simple lines of code.
Any suggestions on how to trigger the code you suggested.

:

You can change the control source through code.

Me.MyTextBox.ControlSource = whatever

You will need to change them back after you update your table. That would
probably be in the form After Update event.

:

I have a form that displays about fifteen fields via text boxes and combo
boxes.
One particular combo box auto-populates two text boxes by the text box's
control source set to: =[comboboxName].column(2) and
=[comboboxName].column(3) respectivly.
I'd like to have the ability to add a new value into the combo box that is
NOT already in the list. When I do add a new combobox value, I'd like to
temporarily change the control source of the following two text boxes to
allow input.
Is this possible or is there an easier way to accomplish this?

 

[Guest]

LOL, If your ambition is high, then your skill level will become the same.

In that you have only the one table, why do you need a separate form to add
those two pieces of information? I think the better approach would be in
your Not In List procedure, you could just go to a new record and put the
values in on the same form.

Perhaps my approach is all wrong.
I have a single table entitled tblAccess_Control.
The Prmary Key is an AutoNumber.
I have a form entitled frmAccessControl that is used for data entry.
I have another form entitled NewRequestor which contains four fields
(RequestorLast, RequestorFirst, Section, and Phone).
If the combobox cboRequestor LIMIT TO LIST property is set to YES and I use
the NOT IN LIST event to open the form NewRequestor as a pop-up from, is it
possible to enter the data in the four fields of the NewRequestor form and
have it inserted into the appropriate recordset in tblAccess_Control and then
have that data immediately displayed in frmAccessControl?
If you couldn't tell before now, my skill level is low but my ambition is
high.
Thanks again,
Daiuy

It will create a new record. Your acFormAdd argument opens the form to add
new records. This is from Access Help:

acFormAdd The user can add new records but can't edit existing records.

Also, to get the new requestor to show up in the list, you need to do a
requery.

Where are the section and phone entered? In the Requestor form? If so, can
you get them from there before you close the requestor form? Without doing
the code, my thought is that in whatever event in th requestor form closes
the requestor form, check to see if the form you are calling it form is open,
and if it is, update the contorls on the original form from there.

I hope I am making sense and helping here. Please post back if you have
more questions or need clarification.

Here is the code I've tried so far:

Private Sub CboRequestor_NotInList(NewData As String, Response As Integer)
Dim intAnswer As Integer
intAnswer = MsgBox("This appears to be a new Requestor. Add?
(Yes/No)", vbYesNo + vbQuestion)
If intAnswer = vbYes Then
DoCmd.OpenForm "New Requestor", , , TxtID =
Forms![frmAccessControl]![ID], acFormAdd, acDialog
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

The code above is not working. I've played with it and played with it, but
no success. It inputs the correct data in the correct fields but it in a new
record by itself.

I also tried:

Private Sub CboRequestor_NotInList(NewData As String, Response As Integer)
Response = acDataErrContinue
Me.Section.ControlSource = tblAccess_Control!Section
Me.Telephone.ControlSource = tblAccess_Control!Telephone
End Sub
I've played with this one as well but I keep getting an compile error:
Argument not Optional.

What I'd really like to happen is for the user to type in the name of the
requestor in the requestor's combobox. As he types, a name that is already
in the list will be displayed. The user can than TAB or ENTER to the next
textbox which is Section which is automatically filled by the control source
set to: =[cboRequestor].column(3) and TAB to the next text box which is
Telephone that has its control source set to: =[cboRequestor].column(4).
However, if the user types in a requestor who is not in the list, I'd like to
capture the rerquestor's name and allow the user to input the section and
phone number.

If I set the LIMIT TO LIST property for cboRequestor to NO, How do I get the
Me.Section.ControlSource = tblAccess_Control!Section to fire? And then
how to I set it back to column(3) after the edit.

If I set the Limit to List property to YES, I can see where I can have the
NOT IN LIST event to trigger the code however I don't have the right code.
Please help.

:

Klatuu - Sorry to be such a rock, but I can't seem to get this to work.
I'm getting error messages or the MS ACCESS dialog telling me to select an
item from the list.
The sun is going down now and it time for me to leave today.
I'll attack it again tomorrow.
Please check this string again tomorrow.
Regards,
Daiuy

:

How are you doing your NotInList event? It works like any other event. If
you are trying to use an expression or a macro, it might be a problem, but if
you put it in the NotInList event using VBA it should work just fine.

:

Thank you for your quick response. However I may have put the cart before
the horse.
I was thinking about using the NOT IN LIST event to trigger the change of
the control source. But apparnetly that event - or my understanding of that
event - will not support two simple lines of code.
Any suggestions on how to trigger the code you suggested.

:

You can change the control source through code.

Me.MyTextBox.ControlSource = whatever

You will need to change them back after you update your table. That would
probably be in the form After Update event.

:

I have a form that displays about fifteen fields via text boxes and combo
boxes.
One particular combo box auto-populates two text boxes by the text box's
control source set to: =[comboboxName].column(2) and
=[comboboxName].column(3) respectivly.
I'd like to have the ability to add a new value into the combo box that is
NOT already in the list. When I do add a new combobox value, I'd like to
temporarily change the control source of the following two text boxes to
allow input.
Is this possible or is there an easier way to accomplish this?

 

[Guest]

True enough, I could remove all the bell and whistle features and have the
user type in all the data for every record - but what's the challenge in that.
I was trying to make some semi-redundent entries semi-autonomous.
We have about two dozen Sections in our organization and its usually the
same person from each section making the request. A combo box and two linked
text boxes would have probably sufficed about 80 percent of the time.
I guess I'll leave the combo box as is and remove the
=comboboxname.column(n) code from the two text boxes and just type in the
Sections and phone numbers.
Thanks for all you help.
Jeff O'Donnell

LOL, If your ambition is high, then your skill level will become the same.

In that you have only the one table, why do you need a separate form to add
those two pieces of information? I think the better approach would be in
your Not In List procedure, you could just go to a new record and put the
values in on the same form.

Perhaps my approach is all wrong.
I have a single table entitled tblAccess_Control.
The Prmary Key is an AutoNumber.
I have a form entitled frmAccessControl that is used for data entry.
I have another form entitled NewRequestor which contains four fields
(RequestorLast, RequestorFirst, Section, and Phone).
If the combobox cboRequestor LIMIT TO LIST property is set to YES and I use
the NOT IN LIST event to open the form NewRequestor as a pop-up from, is it
possible to enter the data in the four fields of the NewRequestor form and
have it inserted into the appropriate recordset in tblAccess_Control and then
have that data immediately displayed in frmAccessControl?
If you couldn't tell before now, my skill level is low but my ambition is
high.
Thanks again,
Daiuy

It will create a new record. Your acFormAdd argument opens the form to add
new records. This is from Access Help:

acFormAdd The user can add new records but can't edit existing records.

Also, to get the new requestor to show up in the list, you need to do a
requery.

Where are the section and phone entered? In the Requestor form? If so, can
you get them from there before you close the requestor form? Without doing
the code, my thought is that in whatever event in th requestor form closes
the requestor form, check to see if the form you are calling it form is open,
and if it is, update the contorls on the original form from there.

I hope I am making sense and helping here. Please post back if you have
more questions or need clarification.

:

Here is the code I've tried so far:

Private Sub CboRequestor_NotInList(NewData As String, Response As Integer)
Dim intAnswer As Integer
intAnswer = MsgBox("This appears to be a new Requestor. Add?
(Yes/No)", vbYesNo + vbQuestion)
If intAnswer = vbYes Then
DoCmd.OpenForm "New Requestor", , , TxtID =
Forms![frmAccessControl]![ID], acFormAdd, acDialog
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

The code above is not working. I've played with it and played with it, but
no success. It inputs the correct data in the correct fields but it in a new
record by itself.

I also tried:

Private Sub CboRequestor_NotInList(NewData As String, Response As Integer)
Response = acDataErrContinue
Me.Section.ControlSource = tblAccess_Control!Section
Me.Telephone.ControlSource = tblAccess_Control!Telephone
End Sub
I've played with this one as well but I keep getting an compile error:
Argument not Optional.

What I'd really like to happen is for the user to type in the name of the
requestor in the requestor's combobox. As he types, a name that is already
in the list will be displayed. The user can than TAB or ENTER to the next
textbox which is Section which is automatically filled by the control source
set to: =[cboRequestor].column(3) and TAB to the next text box which is
Telephone that has its control source set to: =[cboRequestor].column(4).
However, if the user types in a requestor who is not in the list, I'd like to
capture the rerquestor's name and allow the user to input the section and
phone number.

If I set the LIMIT TO LIST property for cboRequestor to NO, How do I get the
Me.Section.ControlSource = tblAccess_Control!Section to fire? And then
how to I set it back to column(3) after the edit.

If I set the Limit to List property to YES, I can see where I can have the
NOT IN LIST event to trigger the code however I don't have the right code.
Please help.

:

Klatuu - Sorry to be such a rock, but I can't seem to get this to work.
I'm getting error messages or the MS ACCESS dialog telling me to select an
item from the list.
The sun is going down now and it time for me to leave today.
I'll attack it again tomorrow.
Please check this string again tomorrow.
Regards,
Daiuy

:

How are you doing your NotInList event? It works like any other event. If
you are trying to use an expression or a macro, it might be a problem, but if
you put it in the NotInList event using VBA it should work just fine.

:

Thank you for your quick response. However I may have put the cart before
the horse.
I was thinking about using the NOT IN LIST event to trigger the change of
the control source. But apparnetly that event - or my understanding of that
event - will not support two simple lines of code.
Any suggestions on how to trigger the code you suggested.

:

You can change the control source through code.

Me.MyTextBox.ControlSource = whatever

You will need to change them back after you update your table. That would
probably be in the form After Update event.

:

I have a form that displays about fifteen fields via text boxes and combo
boxes.
One particular combo box auto-populates two text boxes by the text box's
control source set to: =[comboboxName].column(2) and
=[comboboxName].column(3) respectivly.
I'd like to have the ability to add a new value into the combo box that is
NOT already in the list. When I do add a new combobox value, I'd like to
temporarily change the control source of the following two text boxes to
allow input.
Is this possible or is there an easier way to accomplish this?