Add New Record

Could anyone help please?
I use an unbound Combo Box as a method of finding records in a table and tha
is currently working fine. I am wanting to enter an item into the "Find Combo
Box" and the search for it with the result that ; if the item is not in the
list, the programe tells the operator so and gives the oportunity to ADD a
new record to the table and include the field entered into the Find Combo
Box. Without going into te reason why, I need to have two fielda in the new
record so that I can then search the table, pull up the new record with the
two fields completed and the remaining fields open for Edit ( insert the new
data)
The code I use to add a new record and enter the two fields that ate
required for the subsequent search is as follows :-

Private Sub cmb_Find_Box_3_NotInList(NewData As String, Response As Integer)

Dim strSQL1 As String, strSQL2 As String, strMessage As String
Dim rsMyRs As Recordset, dbMyDB As Database

Debug.Print "cmb_HeaderSelect2 = "; Cmb_HeaderSelect2
Me.AllowEdits = True: Me.AllowAdditions = True: Me.AllowDeletions = True
Set dbMyDB = CurrentDb
strMessage = "Do you want to Add " & NewData & " to list?"
If Cmb_HeaderSelect2 = 6 Then ' Store
Set rsMyRs = dbMyDB.OpenRecordset("tbl_Store", dbOpenDynaset)
ElseIf Cmb_HeaderSelect2 = 4 Then ' SUPPLIERS
' not written yet for this option
End If

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
rsMyRs.AddNew
rsMyRs.Fields("StoreName") = NewData
rsMyRs.Fields("StoreType") = cmb_HeaderSelect3
rsMyRs.Update
rsMyRs.Requery
'cmd.CommandText = strSQL1
Else
Exit Sub
End If
rsMyRs.Close: dbMyDB.Close

' At this point a new Record has been added to the Table and contains the
two pieces of information in the relative fields "StoreName" and "StoreType"

I now need to Search the Table for the new Record and set that so I will be
able to "Edit" the blank Fields and store the data relative to the record
that I have just created But the usual rouine that I use to find records will
not find the new record.
Though I have done a Requery of the Recorset earlier, I am thinking that I
need to do a Requery of the Ind Combo Box "cmb_Find_Combo_3". Tried the ones
below but I Just get errors.
' cmb_Find_Box_3.Requery
' DoCmd.Requery "cmb_Find_Box_3"
Me.Box_3.ControlSource = "StoreName" ' Set the Control
Source for Display Box 3
Call AfterUpdate_Process1("StoreName", NewData, 2,
cmb_HeaderSelect3, "0034")

Me.AllowEdits = True: Me.AllowAdditions = False: Me.AllowDeletions = False
End Sub

Is my thinking all wrong here`? Can anyone help please?

thanks RayC

An example of generic code to do what you want is at:

http://www.datastrat.com/Code/NotInListCode.txt

and a sample application using that code is at:

http://www.accessmvp.com/Arvin/NotInListDemo.zip
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


"Ray C" wrote in message
news
Could anyone help please?
I use an unbound Combo Box as a method of finding records in a table and
tha
is currently working fine. I am wanting to enter an item into the "Find
Combo
Box" and the search for it with the result that ; if the item is not in
the
list, the programe tells the operator so and gives the oportunity to ADD a
new record to the table and include the field entered into the Find Combo
Box. Without going into te reason why, I need to have two fielda in the
new
record so that I can then search the table, pull up the new record with
the
two fields completed and the remaining fields open for Edit ( insert the
new
data)
The code I use to add a new record and enter the two fields that ate
required for the subsequent search is as follows :-

Private Sub cmb_Find_Box_3_NotInList(NewData As String, Response As
Integer)

Dim strSQL1 As String, strSQL2 As String, strMessage As String
Dim rsMyRs As Recordset, dbMyDB As Database

Debug.Print "cmb_HeaderSelect2 = "; Cmb_HeaderSelect2
Me.AllowEdits = True: Me.AllowAdditions = True: Me.AllowDeletions =
True
Set dbMyDB = CurrentDb
strMessage = "Do you want to Add " & NewData & " to list?"
If Cmb_HeaderSelect2 = 6 Then ' Store
Set rsMyRs = dbMyDB.OpenRecordset("tbl_Store", dbOpenDynaset)
ElseIf Cmb_HeaderSelect2 = 4 Then ' SUPPLIERS
' not written yet for this option
End If

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
rsMyRs.AddNew
rsMyRs.Fields("StoreName") = NewData
rsMyRs.Fields("StoreType") = cmb_HeaderSelect3
rsMyRs.Update
rsMyRs.Requery
'cmd.CommandText = strSQL1
Else
Exit Sub
End If
rsMyRs.Close: dbMyDB.Close

' At this point a new Record has been added to the Table and contains the
two pieces of information in the relative fields "StoreName" and
"StoreType"

I now need to Search the Table for the new Record and set that so I will
be
able to "Edit" the blank Fields and store the data relative to the record
that I have just created But the usual rouine that I use to find records
will
not find the new record.
Though I have done a Requery of the Recorset earlier, I am thinking that I
need to do a Requery of the Ind Combo Box "cmb_Find_Combo_3". Tried the
ones
below but I Just get errors.
' cmb_Find_Box_3.Requery
' DoCmd.Requery "cmb_Find_Box_3"
Me.Box_3.ControlSource = "StoreName" ' Set the Control
Source for Display Box 3
Call AfterUpdate_Process1("StoreName", NewData, 2,
cmb_HeaderSelect3, "0034")

Me.AllowEdits = True: Me.AllowAdditions = False: Me.AllowDeletions =
False
End Sub

Is my thinking all wrong here`? Can anyone help please?

thanks RayC

To update the combo box's list set the return value of the NotInList event
procedure's Response argument to acDataErrAdded after the new row has been
inserted into the table.

However, as you need to edit the new record would it not be simpler to
merely move the form to a new record and set the default values of the two
controls, e.g.

Private Sub cmb_Find_Box_3_NotInList(NewData As String, Response As Integer)

On Error GoTo Err_Handler

Dim strMessage As String
Dim strStore As String
Dim strStoreType as string
Dim ctrl As Control

Set ctrl = Me.ActiveControl

strMessage = "Add " & NewData & " to list?"
strStore = NewData
strStoreType = Me.[cmb_HeaderSelect3]

Response = acDataErrContinue
ctrl.Undo

If MsgBox(strMessage, vbQuestion + vbYesNo) = vbYes Then
Me.StoreName.DefaultValue = """" & strStore & """"
Me.StoreType.DefaultValue = """" & strStoreType & """"
DoCmd.GoToRecord acForm, Me.Name, acNewRec
End If

Exit_He
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here

End Sub

Note that the DefaultValue property of a control is a string expression
regardless of the data type of the column to which its bound, so should be
wrapped in literal quotes as above regardless of the data type.

In the form's AfterInsert event procedure requery the combo box with:

Me.[cmb_Find_Box]_3.Requery

Ken Sheridan
Stafford, England

"Ray C" wrote:

Could anyone help please?
I use an unbound Combo Box as a method of finding records in a table and tha
is currently working fine. I am wanting to enter an item into the "Find Combo
Box" and the search for it with the result that ; if the item is not in the
list, the programe tells the operator so and gives the oportunity to ADD a
new record to the table and include the field entered into the Find Combo
Box. Without going into te reason why, I need to have two fielda in the new
record so that I can then search the table, pull up the new record with the
two fields completed and the remaining fields open for Edit ( insert the new
data)
The code I use to add a new record and enter the two fields that ate
required for the subsequent search is as follows :-

Private Sub cmb_Find_Box_3_NotInList(NewData As String, Response As Integer)

Dim strSQL1 As String, strSQL2 As String, strMessage As String
Dim rsMyRs As Recordset, dbMyDB As Database

Debug.Print "cmb_HeaderSelect2 = "; Cmb_HeaderSelect2
Me.AllowEdits = True: Me.AllowAdditions = True: Me.AllowDeletions = True
Set dbMyDB = CurrentDb
strMessage = "Do you want to Add " & NewData & " to list?"
If Cmb_HeaderSelect2 = 6 Then ' Store
Set rsMyRs = dbMyDB.OpenRecordset("tbl_Store", dbOpenDynaset)
ElseIf Cmb_HeaderSelect2 = 4 Then ' SUPPLIERS
' not written yet for this option
End If

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
rsMyRs.AddNew
rsMyRs.Fields("StoreName") = NewData
rsMyRs.Fields("StoreType") = cmb_HeaderSelect3
rsMyRs.Update
rsMyRs.Requery
'cmd.CommandText = strSQL1
Else
Exit Sub
End If
rsMyRs.Close: dbMyDB.Close

' At this point a new Record has been added to the Table and contains the
two pieces of information in the relative fields "StoreName" and "StoreType"

I now need to Search the Table for the new Record and set that so I will be
able to "Edit" the blank Fields and store the data relative to the record
that I have just created But the usual rouine that I use to find records will
not find the new record.
Though I have done a Requery of the Recorset earlier, I am thinking that I
need to do a Requery of the Ind Combo Box "cmb_Find_Combo_3". Tried the ones
below but I Just get errors.
' cmb_Find_Box_3.Requery
' DoCmd.Requery "cmb_Find_Box_3"
Me.Box_3.ControlSource = "StoreName" ' Set the Control
Source for Display Box 3
Call AfterUpdate_Process1("StoreName", NewData, 2,
cmb_HeaderSelect3, "0034")

Me.AllowEdits = True: Me.AllowAdditions = False: Me.AllowDeletions = False
End Sub

Is my thinking all wrong here`? Can anyone help please?

thanks RayC

Hi Ken. Thanks for this but when I try to run the routine i get a "Compile
Error" that stops the execution of the Sub , highlighting the Sub name in
yellow and highlighting the part ".StoreName" in Blue the line
Me.StoreName.DefaultValue = """" & strStore & """"
and giving an error message "Compile Error. method or Data Member not found"
If I REM out that particular line, I get the same error on the next very
similar line. Am I missing some parenthasis here?
Any thoughts please
Ray C


"Ken Sheridan" wrote:

To update the combo box's list set the return value of the NotInList event
procedure's Response argument to acDataErrAdded after the new row has been
inserted into the table.

However, as you need to edit the new record would it not be simpler to
merely move the form to a new record and set the default values of the two
controls, e.g.

Private Sub cmb_Find_Box_3_NotInList(NewData As String, Response As Integer)

On Error GoTo Err_Handler

Dim strMessage As String
Dim strStore As String
Dim strStoreType as string
Dim ctrl As Control

Set ctrl = Me.ActiveControl

strMessage = "Add " & NewData & " to list?"
strStore = NewData
strStoreType = Me.[cmb_HeaderSelect3]

Response = acDataErrContinue
ctrl.Undo

If MsgBox(strMessage, vbQuestion + vbYesNo) = vbYes Then
Me.StoreName.DefaultValue = """" & strStore & """"
Me.StoreType.DefaultValue = """" & strStoreType & """"
DoCmd.GoToRecord acForm, Me.Name, acNewRec
End If

Exit_He
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here

End Sub

Note that the DefaultValue property of a control is a string expression
regardless of the data type of the column to which its bound, so should be
wrapped in literal quotes as above regardless of the data type.

In the form's AfterInsert event procedure requery the combo box with:

Me.[cmb_Find_Box]_3.Requery

Ken Sheridan
Stafford, England

"Ray C" wrote:

Could anyone help please?
I use an unbound Combo Box as a method of finding records in a table and tha
is currently working fine. I am wanting to enter an item into the "Find Combo
Box" and the search for it with the result that ; if the item is not in the
list, the programe tells the operator so and gives the oportunity to ADD a
new record to the table and include the field entered into the Find Combo
Box. Without going into te reason why, I need to have two fielda in the new
record so that I can then search the table, pull up the new record with the
two fields completed and the remaining fields open for Edit ( insert the new
data)
The code I use to add a new record and enter the two fields that ate
required for the subsequent search is as follows :-

Private Sub cmb_Find_Box_3_NotInList(NewData As String, Response As Integer)

Dim strSQL1 As String, strSQL2 As String, strMessage As String
Dim rsMyRs As Recordset, dbMyDB As Database

Debug.Print "cmb_HeaderSelect2 = "; Cmb_HeaderSelect2
Me.AllowEdits = True: Me.AllowAdditions = True: Me.AllowDeletions = True
Set dbMyDB = CurrentDb
strMessage = "Do you want to Add " & NewData & " to list?"
If Cmb_HeaderSelect2 = 6 Then ' Store
Set rsMyRs = dbMyDB.OpenRecordset("tbl_Store", dbOpenDynaset)
ElseIf Cmb_HeaderSelect2 = 4 Then ' SUPPLIERS
' not written yet for this option
End If

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
rsMyRs.AddNew
rsMyRs.Fields("StoreName") = NewData
rsMyRs.Fields("StoreType") = cmb_HeaderSelect3
rsMyRs.Update
rsMyRs.Requery
'cmd.CommandText = strSQL1
Else
Exit Sub
End If
rsMyRs.Close: dbMyDB.Close

' At this point a new Record has been added to the Table and contains the
two pieces of information in the relative fields "StoreName" and "StoreType"

I now need to Search the Table for the new Record and set that so I will be
able to "Edit" the blank Fields and store the data relative to the record
that I have just created But the usual rouine that I use to find records will
not find the new record.
Though I have done a Requery of the Recorset earlier, I am thinking that I
need to do a Requery of the Ind Combo Box "cmb_Find_Combo_3". Tried the ones
below but I Just get errors.
' cmb_Find_Box_3.Requery
' DoCmd.Requery "cmb_Find_Box_3"
Me.Box_3.ControlSource = "StoreName" ' Set the Control
Source for Display Box 3
Call AfterUpdate_Process1("StoreName", NewData, 2,
cmb_HeaderSelect3, "0034")

Me.AllowEdits = True: Me.AllowAdditions = False: Me.AllowDeletions = False
End Sub

Is my thinking all wrong here`? Can anyone help please?

thanks RayC

Ray:

The code assumes that the both the cmb_Find_Box_3 and cmb_HeaderSelect3
combo boxes are on the current form, which is bound to the underlying table
or query, and that the form includes controls named StoreName and StoreType.
Firstly make sure that the names of these controls in the code are Exactly
the same as their actual names, remembering to warp the name in square
brackets if it includes a space or other special character (if in doubt use
the brackets: [StoreName] and [StoreType] If these last two are columns in
the underlying table or query, but don't have controls on the form bound to
them you'll need to add such controls so that you can set their DefaultValue
properties. If you don't want to see them set their Visible property to
False (No).

Ken Sheridan
Stafford, England

"Ray C" wrote:

Hi Ken. Thanks for this but when I try to run the routine i get a "Compile
Error" that stops the execution of the Sub , highlighting the Sub name in
yellow and highlighting the part ".StoreName" in Blue the line
Me.StoreName.DefaultValue = """" & strStore & """"
and giving an error message "Compile Error. method or Data Member not found"
If I REM out that particular line, I get the same error on the next very
similar line. Am I missing some parenthasis here?
Any thoughts please
Ray C


"Ken Sheridan" wrote:

To update the combo box's list set the return value of the NotInList event
procedure's Response argument to acDataErrAdded after the new row has been
inserted into the table.

However, as you need to edit the new record would it not be simpler to
merely move the form to a new record and set the default values of the two
controls, e.g.

Private Sub cmb_Find_Box_3_NotInList(NewData As String, Response As Integer)

On Error GoTo Err_Handler

Dim strMessage As String
Dim strStore As String
Dim strStoreType as string
Dim ctrl As Control

Set ctrl = Me.ActiveControl

strMessage = "Add " & NewData & " to list?"
strStore = NewData
strStoreType = Me.[cmb_HeaderSelect3]

Response = acDataErrContinue
ctrl.Undo

If MsgBox(strMessage, vbQuestion + vbYesNo) = vbYes Then
Me.StoreName.DefaultValue = """" & strStore & """"
Me.StoreType.DefaultValue = """" & strStoreType & """"
DoCmd.GoToRecord acForm, Me.Name, acNewRec
End If

Exit_He
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here

End Sub

Note that the DefaultValue property of a control is a string expression
regardless of the data type of the column to which its bound, so should be
wrapped in literal quotes as above regardless of the data type.

In the form's AfterInsert event procedure requery the combo box with:

Me.[cmb_Find_Box]_3.Requery

Ken Sheridan
Stafford, England

"Ray C" wrote:

Could anyone help please?
I use an unbound Combo Box as a method of finding records in a table and tha
is currently working fine. I am wanting to enter an item into the "Find Combo
Box" and the search for it with the result that ; if the item is not in the
list, the programe tells the operator so and gives the oportunity to ADD a
new record to the table and include the field entered into the Find Combo
Box. Without going into te reason why, I need to have two fielda in the new
record so that I can then search the table, pull up the new record with the
two fields completed and the remaining fields open for Edit ( insert the new
data)
The code I use to add a new record and enter the two fields that ate
required for the subsequent search is as follows :-

Private Sub cmb_Find_Box_3_NotInList(NewData As String, Response As Integer)

Dim strSQL1 As String, strSQL2 As String, strMessage As String
Dim rsMyRs As Recordset, dbMyDB As Database

Debug.Print "cmb_HeaderSelect2 = "; Cmb_HeaderSelect2
Me.AllowEdits = True: Me.AllowAdditions = True: Me.AllowDeletions = True
Set dbMyDB = CurrentDb
strMessage = "Do you want to Add " & NewData & " to list?"
If Cmb_HeaderSelect2 = 6 Then ' Store
Set rsMyRs = dbMyDB.OpenRecordset("tbl_Store", dbOpenDynaset)
ElseIf Cmb_HeaderSelect2 = 4 Then ' SUPPLIERS
' not written yet for this option
End If

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
rsMyRs.AddNew
rsMyRs.Fields("StoreName") = NewData
rsMyRs.Fields("StoreType") = cmb_HeaderSelect3
rsMyRs.Update
rsMyRs.Requery
'cmd.CommandText = strSQL1
Else
Exit Sub
End If
rsMyRs.Close: dbMyDB.Close

' At this point a new Record has been added to the Table and contains the
two pieces of information in the relative fields "StoreName" and "StoreType"

I now need to Search the Table for the new Record and set that so I will be
able to "Edit" the blank Fields and store the data relative to the record
that I have just created But the usual rouine that I use to find records will
not find the new record.
Though I have done a Requery of the Recorset earlier, I am thinking that I
need to do a Requery of the Ind Combo Box "cmb_Find_Combo_3". Tried the ones
below but I Just get errors.
' cmb_Find_Box_3.Requery
' DoCmd.Requery "cmb_Find_Box_3"
Me.Box_3.ControlSource = "StoreName" ' Set the Control
Source for Display Box 3
Call AfterUpdate_Process1("StoreName", NewData, 2,
cmb_HeaderSelect3, "0034")

Me.AllowEdits = True: Me.AllowAdditions = False: Me.AllowDeletions = False
End Sub

Is my thinking all wrong here`? Can anyone help please?

thanks RayC

Hi ken
Thanks for this but after I sent you the reply, I worked out a number of
things.
1. that I needed to set the allow additions to true.
2. that the Bound Text Box the sits underneath the Unbound Combo Find box is
called simply "Box_3" (for a variety of reasons)
3. that I do not have a bound text Box for "StoreType" so I have added one

All seems to be OK and I do not now get the erros message exept that I only
get the last record in the table displayed when I run the routine. In
checking the table itself there is no new item added.
my modification to the code is

Me.Box_3.DefaultValue = """" & strStore & """"
Me.StoreType.DefaultValue = """" & strStoreType & """"
cmb_Find_Box_3.Visible = False: cmb_Find_Box_4.Visible = False
Box_3.Visible = True: Box_4.Visible = True

DoCmd.GoToRecord acForm, Me.Name, acNewRec

At the begining I have added
Me.AllowEdits = True: Me.AllowAdditions = True: Me.AllowDeletions = True
and the coverse at the end

I must still be doing something wrong.
Hope you can put your finger on it, thanks RayC

P.S.
Is there a way to change a Text Box into a Combo box and back again under
program control?


"Ken Sheridan" wrote:

Ray:

The code assumes that the both the cmb_Find_Box_3 and cmb_HeaderSelect3
combo boxes are on the current form, which is bound to the underlying table
or query, and that the form includes controls named StoreName and StoreType.
Firstly make sure that the names of these controls in the code are Exactly
the same as their actual names, remembering to warp the name in square
brackets if it includes a space or other special character (if in doubt use
the brackets: [StoreName] and [StoreType] If these last two are columns in
the underlying table or query, but don't have controls on the form bound to
them you'll need to add such controls so that you can set their DefaultValue
properties. If you don't want to see them set their Visible property to
False (No).

Ken Sheridan
Stafford, England

"Ray C" wrote:

Hi Ken. Thanks for this but when I try to run the routine i get a "Compile
Error" that stops the execution of the Sub , highlighting the Sub name in
yellow and highlighting the part ".StoreName" in Blue the line
Me.StoreName.DefaultValue = """" & strStore & """"
and giving an error message "Compile Error. method or Data Member not found"
If I REM out that particular line, I get the same error on the next very
similar line. Am I missing some parenthasis here?
Any thoughts please
Ray C


"Ken Sheridan" wrote:

To update the combo box's list set the return value of the NotInList event
procedure's Response argument to acDataErrAdded after the new row has been
inserted into the table.

However, as you need to edit the new record would it not be simpler to
merely move the form to a new record and set the default values of the two
controls, e.g.

Private Sub cmb_Find_Box_3_NotInList(NewData As String, Response As Integer)

On Error GoTo Err_Handler

Dim strMessage As String
Dim strStore As String
Dim strStoreType as string
Dim ctrl As Control

Set ctrl = Me.ActiveControl

strMessage = "Add " & NewData & " to list?"
strStore = NewData
strStoreType = Me.[cmb_HeaderSelect3]

Response = acDataErrContinue
ctrl.Undo

If MsgBox(strMessage, vbQuestion + vbYesNo) = vbYes Then
Me.StoreName.DefaultValue = """" & strStore & """"
Me.StoreType.DefaultValue = """" & strStoreType & """"
DoCmd.GoToRecord acForm, Me.Name, acNewRec
End If

Exit_He
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here

End Sub

Note that the DefaultValue property of a control is a string expression
regardless of the data type of the column to which its bound, so should be
wrapped in literal quotes as above regardless of the data type.

In the form's AfterInsert event procedure requery the combo box with:

Me.[cmb_Find_Box]_3.Requery

Ken Sheridan
Stafford, England

"Ray C" wrote:

Could anyone help please?
I use an unbound Combo Box as a method of finding records in a table and tha
is currently working fine. I am wanting to enter an item into the "Find Combo
Box" and the search for it with the result that ; if the item is not in the
list, the programe tells the operator so and gives the oportunity to ADD a
new record to the table and include the field entered into the Find Combo
Box. Without going into te reason why, I need to have two fielda in the new
record so that I can then search the table, pull up the new record with the
two fields completed and the remaining fields open for Edit ( insert the new
data)
The code I use to add a new record and enter the two fields that ate
required for the subsequent search is as follows :-

Private Sub cmb_Find_Box_3_NotInList(NewData As String, Response As Integer)

Dim strSQL1 As String, strSQL2 As String, strMessage As String
Dim rsMyRs As Recordset, dbMyDB As Database

Debug.Print "cmb_HeaderSelect2 = "; Cmb_HeaderSelect2
Me.AllowEdits = True: Me.AllowAdditions = True: Me.AllowDeletions = True
Set dbMyDB = CurrentDb
strMessage = "Do you want to Add " & NewData & " to list?"
If Cmb_HeaderSelect2 = 6 Then ' Store
Set rsMyRs = dbMyDB.OpenRecordset("tbl_Store", dbOpenDynaset)
ElseIf Cmb_HeaderSelect2 = 4 Then ' SUPPLIERS
' not written yet for this option
End If

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
rsMyRs.AddNew
rsMyRs.Fields("StoreName") = NewData
rsMyRs.Fields("StoreType") = cmb_HeaderSelect3
rsMyRs.Update
rsMyRs.Requery
'cmd.CommandText = strSQL1
Else
Exit Sub
End If
rsMyRs.Close: dbMyDB.Close

' At this point a new Record has been added to the Table and contains the
two pieces of information in the relative fields "StoreName" and "StoreType"

I now need to Search the Table for the new Record and set that so I will be
able to "Edit" the blank Fields and store the data relative to the record
that I have just created But the usual rouine that I use to find records will
not find the new record.
Though I have done a Requery of the Recorset earlier, I am thinking that I
need to do a Requery of the Ind Combo Box "cmb_Find_Combo_3". Tried the ones
below but I Just get errors.
' cmb_Find_Box_3.Requery
' DoCmd.Requery "cmb_Find_Box_3"
Me.Box_3.ControlSource = "StoreName" ' Set the Control
Source for Display Box 3
Call AfterUpdate_Process1("StoreName", NewData, 2,
cmb_HeaderSelect3, "0034")

Me.AllowEdits = True: Me.AllowAdditions = False: Me.AllowDeletions = False
End Sub

Is my thinking all wrong here`? Can anyone help please?

thanks RayC

Ray:

I think the problem is that you are setting the form's AllowAdditions
property back to False in the same procedure as moving to the new record, so
what's happening is that it is moving to the new record, but then immediately
back to the last record before you can even see its moved momentarily to a
new record. Set it to True at the start, but not back to False at the end.
Then set it back to False in the form's AfterInsert event procedure. You
should then find it moves to a new record, allows you to insert the
additional data, but then doesn't allow you to navigate to another new record
manually.

An important point to understand is that when you set the DefaultValue
property of a control it doesn't 'dirty' the form, i.e. the form doesn't
contain an unsaved new record; that doesn't happen until you add additional
data yourself. One thing this does mean is that a user could decide not to
proceed with adding the new record and navigate to another record, in which
case the AllowAdditions property would stay as True as the AfterInsert event
would not fire. To cater for this you can put the following code in the
form's Current event procedu

Me.AllowAdditions = Me.NewRecord

The only way I can think of changing a control from a text box to a combo
box and vice versa at runtime would be the extremely cumbersome one of
switching between form view and design view and creating and deleting the
controls. There would be a host of problems associated with this, so I don't
think it’s a practical proposition. There is a far easier solution, however,
which is simply to have separate superimposed text box and combo box controls
and to hide/show one or the other. You'd need to make sure that the focus is
moved off a visible one before hiding the it, e.g.

If Me.MyTextBox.Visible Then
Me.MyComboBox.Visible = True
Me.MyComboBox.SetFocus
Me.MyTextBox.Visible = False
Else
Me.MyTextBox.Visible = True
Me.MyTextBox.SetFocus
Me.MyComboBox.Visible = False
End If

You'd set the Visible property of one to True (Yes) and the other to False
(No) in the properties sheet to govern which shows when the form opens. If
they are unbound controls and you need to keep them in sync then you'd need
to assign a value to the hidden one in the visible one's AfterUpdate event
procedure; if they are bound controls then changing the value in one should
automatically be reflected in the other.

Ken Sheridan
Stafford, England

"Ray C" wrote:

Hi ken
Thanks for this but after I sent you the reply, I worked out a number of
things.
1. that I needed to set the allow additions to true.
2. that the Bound Text Box the sits underneath the Unbound Combo Find box is
called simply "Box_3" (for a variety of reasons)
3. that I do not have a bound text Box for "StoreType" so I have added one

All seems to be OK and I do not now get the erros message exept that I only
get the last record in the table displayed when I run the routine. In
checking the table itself there is no new item added.
my modification to the code is

Me.Box_3.DefaultValue = """" & strStore & """"
Me.StoreType.DefaultValue = """" & strStoreType & """"
cmb_Find_Box_3.Visible = False: cmb_Find_Box_4.Visible = False
Box_3.Visible = True: Box_4.Visible = True

DoCmd.GoToRecord acForm, Me.Name, acNewRec

At the begining I have added
Me.AllowEdits = True: Me.AllowAdditions = True: Me.AllowDeletions = True
and the coverse at the end

I must still be doing something wrong.
Hope you can put your finger on it, thanks RayC

P.S.
Is there a way to change a Text Box into a Combo box and back again under
program control?


"Ken Sheridan" wrote:

Ray:

The code assumes that the both the cmb_Find_Box_3 and cmb_HeaderSelect3
combo boxes are on the current form, which is bound to the underlying table
or query, and that the form includes controls named StoreName and StoreType.
Firstly make sure that the names of these controls in the code are Exactly
the same as their actual names, remembering to warp the name in square
brackets if it includes a space or other special character (if in doubt use
the brackets: [StoreName] and [StoreType] If these last two are columns in
the underlying table or query, but don't have controls on the form bound to
them you'll need to add such controls so that you can set their DefaultValue
properties. If you don't want to see them set their Visible property to
False (No).

Ken Sheridan
Stafford, England

"Ray C" wrote:

Hi Ken. Thanks for this but when I try to run the routine i get a "Compile
Error" that stops the execution of the Sub , highlighting the Sub name in
yellow and highlighting the part ".StoreName" in Blue the line
Me.StoreName.DefaultValue = """" & strStore & """"
and giving an error message "Compile Error. method or Data Member not found"
If I REM out that particular line, I get the same error on the next very
similar line. Am I missing some parenthasis here?
Any thoughts please
Ray C


"Ken Sheridan" wrote:

To update the combo box's list set the return value of the NotInList event
procedure's Response argument to acDataErrAdded after the new row has been
inserted into the table.

However, as you need to edit the new record would it not be simpler to
merely move the form to a new record and set the default values of the two
controls, e.g.

Private Sub cmb_Find_Box_3_NotInList(NewData As String, Response As Integer)

On Error GoTo Err_Handler

Dim strMessage As String
Dim strStore As String
Dim strStoreType as string
Dim ctrl As Control

Set ctrl = Me.ActiveControl

strMessage = "Add " & NewData & " to list?"
strStore = NewData
strStoreType = Me.[cmb_HeaderSelect3]

Response = acDataErrContinue
ctrl.Undo

If MsgBox(strMessage, vbQuestion + vbYesNo) = vbYes Then
Me.StoreName.DefaultValue = """" & strStore & """"
Me.StoreType.DefaultValue = """" & strStoreType & """"
DoCmd.GoToRecord acForm, Me.Name, acNewRec
End If

Exit_He
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here

End Sub

Note that the DefaultValue property of a control is a string expression
regardless of the data type of the column to which its bound, so should be
wrapped in literal quotes as above regardless of the data type.

In the form's AfterInsert event procedure requery the combo box with:

Me.[cmb_Find_Box]_3.Requery

Ken Sheridan
Stafford, England

"Ray C" wrote:

Could anyone help please?
I use an unbound Combo Box as a method of finding records in a table and tha
is currently working fine. I am wanting to enter an item into the "Find Combo
Box" and the search for it with the result that ; if the item is not in the
list, the programe tells the operator so and gives the oportunity to ADD a
new record to the table and include the field entered into the Find Combo
Box. Without going into te reason why, I need to have two fielda in the new
record so that I can then search the table, pull up the new record with the
two fields completed and the remaining fields open for Edit ( insert the new
data)
The code I use to add a new record and enter the two fields that ate
required for the subsequent search is as follows :-

Private Sub cmb_Find_Box_3_NotInList(NewData As String, Response As Integer)

Dim strSQL1 As String, strSQL2 As String, strMessage As String
Dim rsMyRs As Recordset, dbMyDB As Database

Debug.Print "cmb_HeaderSelect2 = "; Cmb_HeaderSelect2
Me.AllowEdits = True: Me.AllowAdditions = True: Me.AllowDeletions = True
Set dbMyDB = CurrentDb
strMessage = "Do you want to Add " & NewData & " to list?"
If Cmb_HeaderSelect2 = 6 Then ' Store
Set rsMyRs = dbMyDB.OpenRecordset("tbl_Store", dbOpenDynaset)
ElseIf Cmb_HeaderSelect2 = 4 Then ' SUPPLIERS
' not written yet for this option
End If

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
rsMyRs.AddNew
rsMyRs.Fields("StoreName") = NewData
rsMyRs.Fields("StoreType") = cmb_HeaderSelect3
rsMyRs.Update
rsMyRs.Requery
'cmd.CommandText = strSQL1
Else
Exit Sub
End If
rsMyRs.Close: dbMyDB.Close

' At this point a new Record has been added to the Table and contains the
two pieces of information in the relative fields "StoreName" and "StoreType"

I now need to Search the Table for the new Record and set that so I will be
able to "Edit" the blank Fields and store the data relative to the record
that I have just created But the usual rouine that I use to find records will
not find the new record.
Though I have done a Requery of the Recorset earlier, I am thinking that I
need to do a Requery of the Ind Combo Box "cmb_Find_Combo_3". Tried the ones
below but I Just get errors.
' cmb_Find_Box_3.Requery
' DoCmd.Requery "cmb_Find_Box_3"
Me.Box_3.ControlSource = "StoreName" ' Set the Control
Source for Display Box 3
Call AfterUpdate_Process1("StoreName", NewData, 2,
cmb_HeaderSelect3, "0034")

Me.AllowEdits = True: Me.AllowAdditions = False: Me.AllowDeletions = False
End Sub

Is my thinking all wrong here`? Can anyone help please?

thanks RayC

Ken, your a wizard, thanks. I though I should re-set the forms properties on
exit but your explanation makes it a lot clearer; and it works.

Yes, I have a Bound Text Box that sits beneath the Unbound Combo Find Box
and I switch visibility when I want to either "Find" or Display / Edit. Just
wondered if there was a way to reduce the thing down to just one box that
changed its character on commant. The two Box option works fine though.

just a quick thsnk you to Arvin Mayer for his input, it was most usefull.
With many thanks RayC

"Ken Sheridan" wrote:

Ray:

I think the problem is that you are setting the form's AllowAdditions
property back to False in the same procedure as moving to the new record, so
what's happening is that it is moving to the new record, but then immediately
back to the last record before you can even see its moved momentarily to a
new record. Set it to True at the start, but not back to False at the end.
Then set it back to False in the form's AfterInsert event procedure. You
should then find it moves to a new record, allows you to insert the
additional data, but then doesn't allow you to navigate to another new record
manually.

An important point to understand is that when you set the DefaultValue
property of a control it doesn't 'dirty' the form, i.e. the form doesn't
contain an unsaved new record; that doesn't happen until you add additional
data yourself. One thing this does mean is that a user could decide not to
proceed with adding the new record and navigate to another record, in which
case the AllowAdditions property would stay as True as the AfterInsert event
would not fire. To cater for this you can put the following code in the
form's Current event procedu

Me.AllowAdditions = Me.NewRecord

The only way I can think of changing a control from a text box to a combo
box and vice versa at runtime would be the extremely cumbersome one of
switching between form view and design view and creating and deleting the
controls. There would be a host of problems associated with this, so I don't
think it’s a practical proposition. There is a far easier solution, however,
which is simply to have separate superimposed text box and combo box controls
and to hide/show one or the other. You'd need to make sure that the focus is
moved off a visible one before hiding the it, e.g.

If Me.MyTextBox.Visible Then
Me.MyComboBox.Visible = True
Me.MyComboBox.SetFocus
Me.MyTextBox.Visible = False
Else
Me.MyTextBox.Visible = True
Me.MyTextBox.SetFocus
Me.MyComboBox.Visible = False
End If

You'd set the Visible property of one to True (Yes) and the other to False
(No) in the properties sheet to govern which shows when the form opens. If
they are unbound controls and you need to keep them in sync then you'd need
to assign a value to the hidden one in the visible one's AfterUpdate event
procedure; if they are bound controls then changing the value in one should
automatically be reflected in the other.

Ken Sheridan
Stafford, England

"Ray C" wrote:

Hi ken
Thanks for this but after I sent you the reply, I worked out a number of
things.
1. that I needed to set the allow additions to true.
2. that the Bound Text Box the sits underneath the Unbound Combo Find box is
called simply "Box_3" (for a variety of reasons)
3. that I do not have a bound text Box for "StoreType" so I have added one

All seems to be OK and I do not now get the erros message exept that I only
get the last record in the table displayed when I run the routine. In
checking the table itself there is no new item added.
my modification to the code is

Me.Box_3.DefaultValue = """" & strStore & """"
Me.StoreType.DefaultValue = """" & strStoreType & """"
cmb_Find_Box_3.Visible = False: cmb_Find_Box_4.Visible = False
Box_3.Visible = True: Box_4.Visible = True

DoCmd.GoToRecord acForm, Me.Name, acNewRec

At the begining I have added
Me.AllowEdits = True: Me.AllowAdditions = True: Me.AllowDeletions = True
and the coverse at the end

I must still be doing something wrong.
Hope you can put your finger on it, thanks RayC

P.S.
Is there a way to change a Text Box into a Combo box and back again under
program control?


"Ken Sheridan" wrote:

Ray:

The code assumes that the both the cmb_Find_Box_3 and cmb_HeaderSelect3
combo boxes are on the current form, which is bound to the underlying table
or query, and that the form includes controls named StoreName and StoreType.
Firstly make sure that the names of these controls in the code are Exactly
the same as their actual names, remembering to warp the name in square
brackets if it includes a space or other special character (if in doubt use
the brackets: [StoreName] and [StoreType] If these last two are columns in
the underlying table or query, but don't have controls on the form bound to
them you'll need to add such controls so that you can set their DefaultValue
properties. If you don't want to see them set their Visible property to
False (No).

Ken Sheridan
Stafford, England

"Ray C" wrote:

Hi Ken. Thanks for this but when I try to run the routine i get a "Compile
Error" that stops the execution of the Sub , highlighting the Sub name in
yellow and highlighting the part ".StoreName" in Blue the line
Me.StoreName.DefaultValue = """" & strStore & """"
and giving an error message "Compile Error. method or Data Member not found"
If I REM out that particular line, I get the same error on the next very
similar line. Am I missing some parenthasis here?
Any thoughts please
Ray C


"Ken Sheridan" wrote:

To update the combo box's list set the return value of the NotInList event
procedure's Response argument to acDataErrAdded after the new row has been
inserted into the table.

However, as you need to edit the new record would it not be simpler to
merely move the form to a new record and set the default values of the two
controls, e.g.

Private Sub cmb_Find_Box_3_NotInList(NewData As String, Response As Integer)

On Error GoTo Err_Handler

Dim strMessage As String
Dim strStore As String
Dim strStoreType as string
Dim ctrl As Control

Set ctrl = Me.ActiveControl

strMessage = "Add " & NewData & " to list?"
strStore = NewData
strStoreType = Me.[cmb_HeaderSelect3]

Response = acDataErrContinue
ctrl.Undo

If MsgBox(strMessage, vbQuestion + vbYesNo) = vbYes Then
Me.StoreName.DefaultValue = """" & strStore & """"
Me.StoreType.DefaultValue = """" & strStoreType & """"
DoCmd.GoToRecord acForm, Me.Name, acNewRec
End If

Exit_He
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here

End Sub

Note that the DefaultValue property of a control is a string expression
regardless of the data type of the column to which its bound, so should be
wrapped in literal quotes as above regardless of the data type.

In the form's AfterInsert event procedure requery the combo box with:

Me.[cmb_Find_Box]_3.Requery

Ken Sheridan
Stafford, England

"Ray C" wrote:

Could anyone help please?
I use an unbound Combo Box as a method of finding records in a table and tha
is currently working fine. I am wanting to enter an item into the "Find Combo
Box" and the search for it with the result that ; if the item is not in the
list, the programe tells the operator so and gives the oportunity to ADD a
new record to the table and include the field entered into the Find Combo
Box. Without going into te reason why, I need to have two fielda in the new
record so that I can then search the table, pull up the new record with the
two fields completed and the remaining fields open for Edit ( insert the new
data)
The code I use to add a new record and enter the two fields that ate
required for the subsequent search is as follows :-

Private Sub cmb_Find_Box_3_NotInList(NewData As String, Response As Integer)

Dim strSQL1 As String, strSQL2 As String, strMessage As String
Dim rsMyRs As Recordset, dbMyDB As Database

Debug.Print "cmb_HeaderSelect2 = "; Cmb_HeaderSelect2
Me.AllowEdits = True: Me.AllowAdditions = True: Me.AllowDeletions = True
Set dbMyDB = CurrentDb
strMessage = "Do you want to Add " & NewData & " to list?"
If Cmb_HeaderSelect2 = 6 Then ' Store
Set rsMyRs = dbMyDB.OpenRecordset("tbl_Store", dbOpenDynaset)
ElseIf Cmb_HeaderSelect2 = 4 Then ' SUPPLIERS
' not written yet for this option
End If

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
rsMyRs.AddNew
rsMyRs.Fields("StoreName") = NewData
rsMyRs.Fields("StoreType") = cmb_HeaderSelect3
rsMyRs.Update
rsMyRs.Requery
'cmd.CommandText = strSQL1
Else
Exit Sub
End If
rsMyRs.Close: dbMyDB.Close

' At this point a new Record has been added to the Table and contains the
two pieces of information in the relative fields "StoreName" and "StoreType"

I now need to Search the Table for the new Record and set that so I will be
able to "Edit" the blank Fields and store the data relative to the record
that I have just created But the usual rouine that I use to find records will
not find the new record.
Though I have done a Requery of the Recorset earlier, I am thinking that I
need to do a Requery of the Ind Combo Box "cmb_Find_Combo_3". Tried the ones
below but I Just get errors.
' cmb_Find_Box_3.Requery
' DoCmd.Requery "cmb_Find_Box_3"
Me.Box_3.ControlSource = "StoreName" ' Set the Control
Source for Display Box 3
Call AfterUpdate_Process1("StoreName", NewData, 2,
cmb_HeaderSelect3, "0034")

Me.AllowEdits = True: Me.AllowAdditions = False: Me.AllowDeletions = False
End Sub

Is my thinking all wrong here`? Can anyone help please?

thanks RayC