allow new data to be saved/shown in combo boxes

  • Thread starter Thread starter auntyjack
  • Start date Start date
A

auntyjack

I would like the text to allow data to be added to combo
boxes as you go without having to go out and add the text
to the underlying table - I have it set for 'limit it to
list' NO. I want the new data to show in the drop down
lists.there will be a lot of new items
I know this is not always good but more suitable for my
database.
Also the text for and correct location (eg After Update)
to ensure that new items added to the underlying forms
will show in the combo boxes when you need to go out of
the form to add missing items in combo boxes. there are
not many new items
These are 2 different situations
 
I've tried the code on the web site but still can't get
it - my table is "ModelNo" and field name "model number"
I've tried chaging areas of the code to make it add the
data to the table and show in the list but not successful.
This is what I have (don't know what cbxAEName refers to):
Private Sub cbxAENAME_NotInList(NewData As String,
Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String


strMsg = "'" & NewData & "' is not an available Model
Number " & vbCrLf & vbCrLf
strMsg = strMsg & "Do you want to associate the new
Name to the current DLSAF?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link
or No to re-type it."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new
name?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("tblModelNO",
dbOpenDynaset)
On Error Resume Next
rs.addnew
rs!modelnumber = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If

End If

rs.close
Set rs = Nothing
Set db = Nothing
End Sub
 
Hi

CbxAEName should be the name of your combo box (so change that if
necessary - if you right mouse click on the combo box and choose properties
& then choose "not in list" and then click on the ... then choose code
builder it will put the sub & end sub lines in for you then
if the table is called ModelNo and the field is Model Number then you'll
need to modify your code as follows:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = NewData & " is not an available Model number " & vbCrLf &
vbCrLf
strMsg = strMsg & "Do you want to associate the new Name to the current
Model Number List?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link or No to re-type
it."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new model number?") = vbNo
Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("ModelNo", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs![model number] = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If

End If

rs.Close
Set rs = Nothing
Set db = Nothing

End Sub
---
let me know how you go

Cheers
JulieD



"which code refers to table names/record names"
 
Still not successful - I understand combo box name (it is
also ModelNo - I changed it) and know where to write the
code, but not good on code.
I'm not even getting the message that the name is not on
the list when I try which is the first part of the code.
I have Model Names in a table of thier own from which the
combo box gets info and stores it to the the customers
record. Are there any other settings I should set that may
have an influence on it - I have got - Limit to list - NO.
mostly use Wizards but can do a bit by myself.
Also can you provide info on how to update the list in the
current form when you go directly to the form/table with
the combo box info - add more names, then go back the the
current form without having to close it and come back to
make the new ones appear on the list - I currenly update
the info by pressing F9 in the field but want something
better for others to use. Your help is very much
appreciated as I live in a faily isolated area and trying
to write a database for work to record info on customers
plant/equipment for others to use. Work is also in a small
country town so a bit hard to find help and others who
know how to do these things.
Thanks
-----Original Message-----
Hi

CbxAEName should be the name of your combo box (so change that if
necessary - if you right mouse click on the combo box and choose properties
& then choose "not in list" and then click on the ... then choose code
builder it will put the sub & end sub lines in for you then
if the table is called ModelNo and the field is Model Number then you'll
need to modify your code as follows:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = NewData & " is not an available Model number " & vbCrLf &
vbCrLf
strMsg = strMsg & "Do you want to associate the new Name to the current
Model Number List?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link or No to re-type
it."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new model number?") = vbNo
Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("ModelNo", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs![model number] = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If

End If

rs.Close
Set rs = Nothing
Set db = Nothing

End Sub
---
let me know how you go

Cheers
JulieD



"which code refers to table names/record names"
I've tried the code on the web site but still can't get
it - my table is "ModelNo" and field name "model number"
I've tried chaging areas of the code to make it add the
data to the table and show in the list but not successful.
This is what I have (don't know what cbxAEName refers to):
Private Sub cbxAENAME_NotInList(NewData As String,
Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String


strMsg = "'" & NewData & "' is not an available Model
Number " & vbCrLf & vbCrLf
strMsg = strMsg & "Do you want to associate the new
Name to the current DLSAF?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link
or No to re-type it."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new
name?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("tblModelNO",
dbOpenDynaset)
On Error Resume Next
rs.addnew
rs!modelnumber = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If

End If

rs.close
Set rs = Nothing
Set db = Nothing
End Sub
Click to expand...


.
Click to expand...
 
Hi

compact / repair & zip your db and sent it to me, if you like and i'll have
a look at it.

Cheers
julieD

auntyjack said:
Still not successful - I understand combo box name (it is
also ModelNo - I changed it) and know where to write the
code, but not good on code.
I'm not even getting the message that the name is not on
the list when I try which is the first part of the code.
I have Model Names in a table of thier own from which the
combo box gets info and stores it to the the customers
record. Are there any other settings I should set that may
have an influence on it - I have got - Limit to list - NO.
mostly use Wizards but can do a bit by myself.
Also can you provide info on how to update the list in the
current form when you go directly to the form/table with
the combo box info - add more names, then go back the the
current form without having to close it and come back to
make the new ones appear on the list - I currenly update
the info by pressing F9 in the field but want something
better for others to use. Your help is very much
appreciated as I live in a faily isolated area and trying
to write a database for work to record info on customers
plant/equipment for others to use. Work is also in a small
country town so a bit hard to find help and others who
know how to do these things.
Thanks
-----Original Message-----
Hi

CbxAEName should be the name of your combo box (so change that if
necessary - if you right mouse click on the combo box and choose properties
& then choose "not in list" and then click on the ... then choose code
builder it will put the sub & end sub lines in for you then
if the table is called ModelNo and the field is Model Number then you'll
need to modify your code as follows:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = NewData & " is not an available Model number " & vbCrLf &
vbCrLf
strMsg = strMsg & "Do you want to associate the new Name to the current
Model Number List?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link or No to re-type
it."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new model number?") = vbNo
Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("ModelNo", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs![model number] = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If

End If

rs.Close
Set rs = Nothing
Set db = Nothing

End Sub
---
let me know how you go

Cheers
JulieD



"which code refers to table names/record names"
I've tried the code on the web site but still can't get
it - my table is "ModelNo" and field name "model number"
I've tried chaging areas of the code to make it add the
data to the table and show in the list but not successful.
This is what I have (don't know what cbxAEName refers to):
Private Sub cbxAENAME_NotInList(NewData As String,
Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String


strMsg = "'" & NewData & "' is not an available Model
Number " & vbCrLf & vbCrLf
strMsg = strMsg & "Do you want to associate the new
Name to the current DLSAF?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link
or No to re-type it."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new
name?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("tblModelNO",
dbOpenDynaset)
On Error Resume Next
rs.addnew
rs!modelnumber = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If

End If

rs.close
Set rs = Nothing
Set db = Nothing
End Sub
-----Original Message-----
Hi

you might like to have a look at
http://www.mvps.org/access/forms/frm0015.htm
"Add item to combo box using OnNotinList event"

cheers
JulieD

in message
I would like the text to allow data to be added to combo
boxes as you go without having to go out and add the
text
to the underlying table - I have it set for 'limit it to
list' NO. I want the new data to show in the drop down
lists.there will be a lot of new items
I know this is not always good but more suitable for my
database.
Also the text for and correct location (eg After Update)
to ensure that new items added to the underlying forms
will show in the combo boxes when you need to go out of
the form to add missing items in combo boxes. there are
not many new items
These are 2 different situations


.
Click to expand...


.
Click to expand...
Click to expand...
 
hi

that's email it direct to me, not to the ng (email address is julied_ng @
hcts dot net dot au)

auntyjack said:
Still not successful - I understand combo box name (it is
also ModelNo - I changed it) and know where to write the
code, but not good on code.
I'm not even getting the message that the name is not on
the list when I try which is the first part of the code.
I have Model Names in a table of thier own from which the
combo box gets info and stores it to the the customers
record. Are there any other settings I should set that may
have an influence on it - I have got - Limit to list - NO.
mostly use Wizards but can do a bit by myself.
Also can you provide info on how to update the list in the
current form when you go directly to the form/table with
the combo box info - add more names, then go back the the
current form without having to close it and come back to
make the new ones appear on the list - I currenly update
the info by pressing F9 in the field but want something
better for others to use. Your help is very much
appreciated as I live in a faily isolated area and trying
to write a database for work to record info on customers
plant/equipment for others to use. Work is also in a small
country town so a bit hard to find help and others who
know how to do these things.
Thanks
-----Original Message-----
Hi

CbxAEName should be the name of your combo box (so change that if
necessary - if you right mouse click on the combo box and choose properties
& then choose "not in list" and then click on the ... then choose code
builder it will put the sub & end sub lines in for you then
if the table is called ModelNo and the field is Model Number then you'll
need to modify your code as follows:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = NewData & " is not an available Model number " & vbCrLf &
vbCrLf
strMsg = strMsg & "Do you want to associate the new Name to the current
Model Number List?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link or No to re-type
it."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new model number?") = vbNo
Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("ModelNo", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs![model number] = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If

End If

rs.Close
Set rs = Nothing
Set db = Nothing

End Sub
---
let me know how you go

Cheers
JulieD



"which code refers to table names/record names"
I've tried the code on the web site but still can't get
it - my table is "ModelNo" and field name "model number"
I've tried chaging areas of the code to make it add the
data to the table and show in the list but not successful.
This is what I have (don't know what cbxAEName refers to):
Private Sub cbxAENAME_NotInList(NewData As String,
Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String


strMsg = "'" & NewData & "' is not an available Model
Number " & vbCrLf & vbCrLf
strMsg = strMsg & "Do you want to associate the new
Name to the current DLSAF?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link
or No to re-type it."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new
name?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("tblModelNO",
dbOpenDynaset)
On Error Resume Next
rs.addnew
rs!modelnumber = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If

End If

rs.close
Set rs = Nothing
Set db = Nothing
End Sub
-----Original Message-----
Hi

you might like to have a look at
http://www.mvps.org/access/forms/frm0015.htm
"Add item to combo box using OnNotinList event"

cheers
JulieD

in message
I would like the text to allow data to be added to combo
boxes as you go without having to go out and add the
text
to the underlying table - I have it set for 'limit it to
list' NO. I want the new data to show in the drop down
lists.there will be a lot of new items
I know this is not always good but more suitable for my
database.
Also the text for and correct location (eg After Update)
to ensure that new items added to the underlying forms
will show in the combo boxes when you need to go out of
the form to add missing items in combo boxes. there are
not many new items
These are 2 different situations


.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top