Combo Box interacting to Table

[KimTong via AccessMonster.com]

Hi,

I have a Combo Box with a limited cust's name from table CUSTOMER. I have no
problem when my users enter a cust name was already on list. Is it possible
MS Access can add automatically a new cust (not on the list) to table
CUSTOMER when the user enter a cust is not on the list?. So when they enter
that certain cust for the next time, that cust already on the list.

Thank you in advance.


KFT

 

[scubadiver]

Put the following in the combo box "not in list" event.



Dim strSql As String
Dim I As Integer
Dim Msg As String

'Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub

Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"

I = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Product type...")
If I = vbYes Then
strSql = "Insert Into lkup_login([LoginName]) " & _
"values ('" & NewData & "');"
CurrentDb.Execute strSql, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If



strSql = "Insert Into lkup_login([LoginName]) " & _
"values ('" & NewData & "');"

lkup_login - table name
LoginName - field name

The combo box "limit to list" property is set to "yes".


Let me know!

 

[Al Campagna]

Kim,
I think what you really want to do, is that when a CustName does not
appear in a combo box (NotInList), the user should be taken to a blank new
tblCustomer record. All the new customer info can be updated there
(Address, Zip, Phone...). After the new customer is added to tblCustomers,
closing that form would return you back to your original "calling" form with
the combo box.
Given that tblCustomers now has a new customer.. a Requery of the combo
box, would now show that name for selection.

In other words, new customers are not added to your database via the
combo box, but by entering them into tblCustomers, which in turn is the
RowSource for your selection combo box.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html
"Find a job that you love... and you'll never work a day in your life."

 

[scubadiver]

After I posted my reply, it hadn't occurred to me that the customer *could*
have extra info so you are correct.

Kim,
I think what you really want to do, is that when a CustName does not
appear in a combo box (NotInList), the user should be taken to a blank new
tblCustomer record. All the new customer info can be updated there
(Address, Zip, Phone...). After the new customer is added to tblCustomers,
closing that form would return you back to your original "calling" form with
the combo box.
Given that tblCustomers now has a new customer.. a Requery of the combo
box, would now show that name for selection.

In other words, new customers are not added to your database via the
combo box, but by entering them into tblCustomers, which in turn is the
RowSource for your selection combo box.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html
"Find a job that you love... and you'll never work a day in your life."

 

[KimTong via AccessMonster.com]

Thank you scubadiver. That's find, I just have a cust name in my Customer
Table. I haven't tried it yet, but I try it in couple hour, let see if it
works.

KFT

Kim,
I think what you really want to do, is that when a CustName does not
appear in a combo box (NotInList), the user should be taken to a blank new
tblCustomer record. All the new customer info can be updated there
(Address, Zip, Phone...). After the new customer is added to tblCustomers,
closing that form would return you back to your original "calling" form with
the combo box.
Given that tblCustomers now has a new customer.. a Requery of the combo
box, would now show that name for selection.

In other words, new customers are not added to your database via the
combo box, but by entering them into tblCustomers, which in turn is the
RowSource for your selection combo box.

Hi,

[quoted text clipped - 10 lines]

KFT

 

[KimTong via AccessMonster.com]

Hi Scubadiver,

I just tried to copy the VB script that you gave me and changed the table &
field name that I have originally. But I still have a little problem. When I
entered a new cust name (not the list), the message comes 'The Text you
entered isn't an item in the list'. Do you know why that message comes up?
Thank you, I am appreciated.

KFT

After I posted my reply, it hadn't occurred to me that the customer *could*
have extra info so you are correct.

Kim,
I think what you really want to do, is that when a CustName does not

[quoted text clipped - 23 lines]

 

[KimTong via AccessMonster.com]

Hi scubadiver,

I still got stuck with this VB script that you gave, the don't allow me to
enter a new cust name on the combobox. Please need help if somebody can
saolve this proble. Thanks....

KFT

Thank you scubadiver. That's find, I just have a cust name in my Customer
Table. I haven't tried it yet, but I try it in couple hour, let see if it
works.

KFT

Kim,
I think what you really want to do, is that when a CustName does not

[quoted text clipped - 14 lines]

 

[Amul]

Try this way.

Create a form for the "tblCustomer" save it. Let's say frmCustomer.
------------------

Edit the form where you are using the combo,

in "Data" tab find "List Items Edit Form" select the "frmCustomer" which
you have created for tblCustomer.

I hope this might be helping you. (I am not sure of your access version - I
am using 2007)

Hi scubadiver,

I still got stuck with this VB script that you gave, the don't allow me to
enter a new cust name on the combobox. Please need help if somebody can
saolve this proble. Thanks....

KFT

Thank you scubadiver. That's find, I just have a cust name in my Customer
Table. I haven't tried it yet, but I try it in couple hour, let see if it
works.

KFT

Kim,
I think what you really want to do, is that when a CustName does not

[quoted text clipped - 14 lines]

KFT

 

[KimTong via AccessMonster.com]

Hi Amul,

I can't find 'List Items Edit Form' property on the form that I have a combo
box. Could you explain it to me more detail, please? Thanks...

KF

Try this way.

Create a form for the "tblCustomer" save it. Let's say frmCustomer.
------------------

Edit the form where you are using the combo,

in "Data" tab find "List Items Edit Form" select the "frmCustomer" which
you have created for tblCustomer.

I hope this might be helping you. (I am not sure of your access version - I
am using 2007)

Hi scubadiver,

[quoted text clipped - 15 lines]