when to refresh form data

[gymphil]

I am using a second form to enter new data, when I return back to the main
form I can only auto fill the productID detail, all the other data entered in
the 'newpart' form is not visible on the main form, Don't know how and where
to refresh the form data, can anyone assist? thankyou

Phil

Private Sub Combo14_AfterUpdate()

Set rs = Me.Recordset.Clone
rs.FindFirst "[productID] = '" & Me![Combo14] & "'"
Me.Bookmark = rs.Bookmark

End Sub

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

Dim Result
Dim Msg As String
Dim CR As String: CR = Chr$(13)
If NewData = "" Then Exit Sub
Msg = "'" & NewData & "' is not in the list." & CR & CR
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, 32 + 4) = 6 Then
DoCmd.OpenForm "newpart", , , , acAdd, acDialog, NewData
End If
Result = DLookup("[productID]", "products", _
"[productID]=""" & NewData & """")
If IsNull(Result) Then
Response = DATA_ERRCONTINUE
MsgBox "Please try again!"
Else
Response = DATA_ERRADDED
End If

End Sub

 

[Klatuu]

Since you are adding the record using a different form, you current form
doesn't know about it yet. You should requery your form in the Not In List
event after the record has been added in the add record form. Then rather
than a DLookup, use the FindFirst method on the form's recordsetclone to
navigate to the newly added record. Here is a modification of your current
code:

Private Sub Combo14_NotInList(NewData As String, Response As Integer)
Dim Msg As String

If NewData = "" Then
Exit Sub
End If

If MsgBox(NewData & "' is not in the list." & vbNewLine & vbNewLine & _
"Do you want to add it?", vbQuestion + vbYesNo) = vbYes Then
DoCmd.OpenForm "newpart", , , , acAdd, acDialog, NewData
Me.Combo14.Undo
DoCmd.OpenForm "newpart", , , , acAdd, acDialog, NewData
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClone
.FindFirst "[ProductID] = """ & NewData & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Else
Me.Combo14.Undo
Response = acDataErrContinue
End If

End Sub

Notice I used the constants vbQuestion, vbYesNo, etc rather than their
values. It makes the code much easier to read. Also, no need to Dim a
variable for a line feed. there are at least two different intrinsic
constants that do that. I prefer vbNewLine, because its purpose is obvious
to the reader.

 

[gymphil]

Dave,

Many thanks for that, I have changed the code and it works great. I would
have liked to use the same form to create new records but got into all sorts
of bother when using me.combo14 = me.productID on the forms on current event.

Thanks again Dave

Phil

Since you are adding the record using a different form, you current form
doesn't know about it yet. You should requery your form in the Not In List
event after the record has been added in the add record form. Then rather
than a DLookup, use the FindFirst method on the form's recordsetclone to
navigate to the newly added record. Here is a modification of your current
code:

Private Sub Combo14_NotInList(NewData As String, Response As Integer)
Dim Msg As String

If NewData = "" Then
Exit Sub
End If

If MsgBox(NewData & "' is not in the list." & vbNewLine & vbNewLine & _
"Do you want to add it?", vbQuestion + vbYesNo) = vbYes Then
DoCmd.OpenForm "newpart", , , , acAdd, acDialog, NewData
Me.Combo14.Undo
DoCmd.OpenForm "newpart", , , , acAdd, acDialog, NewData
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClone
.FindFirst "[ProductID] = """ & NewData & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Else
Me.Combo14.Undo
Response = acDataErrContinue
End If

End Sub

Notice I used the constants vbQuestion, vbYesNo, etc rather than their
values. It makes the code much easier to read. Also, no need to Dim a
variable for a line feed. there are at least two different intrinsic
constants that do that. I prefer vbNewLine, because its purpose is obvious
to the reader.
--
Dave Hargis, Microsoft Access MVP

I am using a second form to enter new data, when I return back to the main
form I can only auto fill the productID detail, all the other data entered in
the 'newpart' form is not visible on the main form, Don't know how and where
to refresh the form data, can anyone assist? thankyou

Phil

Private Sub Combo14_AfterUpdate()

Set rs = Me.Recordset.Clone
rs.FindFirst "[productID] = '" & Me![Combo14] & "'"
Me.Bookmark = rs.Bookmark

End Sub

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

Dim Result
Dim Msg As String
Dim CR As String: CR = Chr$(13)
If NewData = "" Then Exit Sub
Msg = "'" & NewData & "' is not in the list." & CR & CR
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, 32 + 4) = 6 Then
DoCmd.OpenForm "newpart", , , , acAdd, acDialog, NewData
End If
Result = DLookup("[productID]", "products", _
"[productID]=""" & NewData & """")
If IsNull(Result) Then
Response = DATA_ERRCONTINUE
MsgBox "Please try again!"
Else
Response = DATA_ERRADDED
End If

End Sub

 

[Klatuu]

Why do you need to do that?
Combo14 is not a bound control, is it?
--
Dave Hargis, Microsoft Access MVP

Dave,

Many thanks for that, I have changed the code and it works great. I would
have liked to use the same form to create new records but got into all sorts
of bother when using me.combo14 = me.productID on the forms on current event.

Thanks again Dave

Phil

Since you are adding the record using a different form, you current form
doesn't know about it yet. You should requery your form in the Not In List
event after the record has been added in the add record form. Then rather
than a DLookup, use the FindFirst method on the form's recordsetclone to
navigate to the newly added record. Here is a modification of your current
code:

Private Sub Combo14_NotInList(NewData As String, Response As Integer)
Dim Msg As String

If NewData = "" Then
Exit Sub
End If

If MsgBox(NewData & "' is not in the list." & vbNewLine & vbNewLine & _
"Do you want to add it?", vbQuestion + vbYesNo) = vbYes Then
DoCmd.OpenForm "newpart", , , , acAdd, acDialog, NewData
Me.Combo14.Undo
DoCmd.OpenForm "newpart", , , , acAdd, acDialog, NewData
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClone
.FindFirst "[ProductID] = """ & NewData & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Else
Me.Combo14.Undo
Response = acDataErrContinue
End If

End Sub

Notice I used the constants vbQuestion, vbYesNo, etc rather than their
values. It makes the code much easier to read. Also, no need to Dim a
variable for a line feed. there are at least two different intrinsic
constants that do that. I prefer vbNewLine, because its purpose is obvious
to the reader.
--
Dave Hargis, Microsoft Access MVP

I am using a second form to enter new data, when I return back to the main
form I can only auto fill the productID detail, all the other data entered in
the 'newpart' form is not visible on the main form, Don't know how and where
to refresh the form data, can anyone assist? thankyou

Phil

Private Sub Combo14_AfterUpdate()

Set rs = Me.Recordset.Clone
rs.FindFirst "[productID] = '" & Me![Combo14] & "'"
Me.Bookmark = rs.Bookmark

End Sub

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

Dim Result
Dim Msg As String
Dim CR As String: CR = Chr$(13)
If NewData = "" Then Exit Sub
Msg = "'" & NewData & "' is not in the list." & CR & CR
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, 32 + 4) = 6 Then
DoCmd.OpenForm "newpart", , , , acAdd, acDialog, NewData
End If
Result = DLookup("[productID]", "products", _
"[productID]=""" & NewData & """")
If IsNull(Result) Then
Response = DATA_ERRCONTINUE
MsgBox "Please try again!"
Else
Response = DATA_ERRADDED
End If

End Sub

 

[gymphil]

Dave,

No that's correct, combo14 is not bound, that was partly the problem. When
scrolling through the records on the form, the contents of the combobox
didn't change, this was misleading and the advice provided was to use
me.combo14 = me.productID, this kept the combobox in sync with the record
that appeared on the screen. But for some reason this created a conflict when
I tried to use the same form to add records.

Phil

Why do you need to do that?
Combo14 is not a bound control, is it?
--
Dave Hargis, Microsoft Access MVP

Dave,

Many thanks for that, I have changed the code and it works great. I would
have liked to use the same form to create new records but got into all sorts
of bother when using me.combo14 = me.productID on the forms on current event.

Thanks again Dave

Phil

Since you are adding the record using a different form, you current form
doesn't know about it yet. You should requery your form in the Not In List
event after the record has been added in the add record form. Then rather
than a DLookup, use the FindFirst method on the form's recordsetclone to
navigate to the newly added record. Here is a modification of your current
code:

Private Sub Combo14_NotInList(NewData As String, Response As Integer)
Dim Msg As String

If NewData = "" Then
Exit Sub
End If

If MsgBox(NewData & "' is not in the list." & vbNewLine & vbNewLine & _
"Do you want to add it?", vbQuestion + vbYesNo) = vbYes Then
DoCmd.OpenForm "newpart", , , , acAdd, acDialog, NewData
Me.Combo14.Undo
DoCmd.OpenForm "newpart", , , , acAdd, acDialog, NewData
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClone
.FindFirst "[ProductID] = """ & NewData & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Else
Me.Combo14.Undo
Response = acDataErrContinue
End If

End Sub

Notice I used the constants vbQuestion, vbYesNo, etc rather than their
values. It makes the code much easier to read. Also, no need to Dim a
variable for a line feed. there are at least two different intrinsic
constants that do that. I prefer vbNewLine, because its purpose is obvious
to the reader.
--
Dave Hargis, Microsoft Access MVP


:

I am using a second form to enter new data, when I return back to the main
form I can only auto fill the productID detail, all the other data entered in
the 'newpart' form is not visible on the main form, Don't know how and where
to refresh the form data, can anyone assist? thankyou

Phil

Private Sub Combo14_AfterUpdate()

Set rs = Me.Recordset.Clone
rs.FindFirst "[productID] = '" & Me![Combo14] & "'"
Me.Bookmark = rs.Bookmark

End Sub

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

Dim Result
Dim Msg As String
Dim CR As String: CR = Chr$(13)
If NewData = "" Then Exit Sub
Msg = "'" & NewData & "' is not in the list." & CR & CR
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, 32 + 4) = 6 Then
DoCmd.OpenForm "newpart", , , , acAdd, acDialog, NewData
End If
Result = DLookup("[productID]", "products", _
"[productID]=""" & NewData & """")
If IsNull(Result) Then
Response = DATA_ERRCONTINUE
MsgBox "Please try again!"
Else
Response = DATA_ERRADDED
End If

End Sub

 

[Klatuu]

Okay, that makes sense. My preference is to set it to Null so nothing shows
in it:
me.combo14 = Null
--
Dave Hargis, Microsoft Access MVP

Dave,

No that's correct, combo14 is not bound, that was partly the problem. When
scrolling through the records on the form, the contents of the combobox
didn't change, this was misleading and the advice provided was to use
me.combo14 = me.productID, this kept the combobox in sync with the record
that appeared on the screen. But for some reason this created a conflict when
I tried to use the same form to add records.

Phil

Why do you need to do that?
Combo14 is not a bound control, is it?
--
Dave Hargis, Microsoft Access MVP

Dave,

Many thanks for that, I have changed the code and it works great. I would
have liked to use the same form to create new records but got into all sorts
of bother when using me.combo14 = me.productID on the forms on current event.

Thanks again Dave

Phil


:

Since you are adding the record using a different form, you current form
doesn't know about it yet. You should requery your form in the Not In List
event after the record has been added in the add record form. Then rather
than a DLookup, use the FindFirst method on the form's recordsetclone to
navigate to the newly added record. Here is a modification of your current
code:

Private Sub Combo14_NotInList(NewData As String, Response As Integer)
Dim Msg As String

If NewData = "" Then
Exit Sub
End If

If MsgBox(NewData & "' is not in the list." & vbNewLine & vbNewLine & _
"Do you want to add it?", vbQuestion + vbYesNo) = vbYes Then
DoCmd.OpenForm "newpart", , , , acAdd, acDialog, NewData
Me.Combo14.Undo
DoCmd.OpenForm "newpart", , , , acAdd, acDialog, NewData
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClone
.FindFirst "[ProductID] = """ & NewData & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Else
Me.Combo14.Undo
Response = acDataErrContinue
End If

End Sub

Notice I used the constants vbQuestion, vbYesNo, etc rather than their
values. It makes the code much easier to read. Also, no need to Dim a
variable for a line feed. there are at least two different intrinsic
constants that do that. I prefer vbNewLine, because its purpose is obvious
to the reader.
--
Dave Hargis, Microsoft Access MVP


:

I am using a second form to enter new data, when I return back to the main
form I can only auto fill the productID detail, all the other data entered in
the 'newpart' form is not visible on the main form, Don't know how and where
to refresh the form data, can anyone assist? thankyou

Phil

Private Sub Combo14_AfterUpdate()

Set rs = Me.Recordset.Clone
rs.FindFirst "[productID] = '" & Me![Combo14] & "'"
Me.Bookmark = rs.Bookmark

End Sub

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

Dim Result
Dim Msg As String
Dim CR As String: CR = Chr$(13)
If NewData = "" Then Exit Sub
Msg = "'" & NewData & "' is not in the list." & CR & CR
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, 32 + 4) = 6 Then
DoCmd.OpenForm "newpart", , , , acAdd, acDialog, NewData
End If
Result = DLookup("[productID]", "products", _
"[productID]=""" & NewData & """")
If IsNull(Result) Then
Response = DATA_ERRCONTINUE
MsgBox "Please try again!"
Else
Response = DATA_ERRADDED
End If

End Sub

 

[gymphil]

I have got to keep the contents of the combo box visible otherwise I will not
know what record the fields on the form relate to!

Phil

Okay, that makes sense. My preference is to set it to Null so nothing shows
in it:
me.combo14 = Null
--
Dave Hargis, Microsoft Access MVP

Dave,

No that's correct, combo14 is not bound, that was partly the problem. When
scrolling through the records on the form, the contents of the combobox
didn't change, this was misleading and the advice provided was to use
me.combo14 = me.productID, this kept the combobox in sync with the record
that appeared on the screen. But for some reason this created a conflict when
I tried to use the same form to add records.

Phil

Why do you need to do that?
Combo14 is not a bound control, is it?
--
Dave Hargis, Microsoft Access MVP


:

Dave,

Many thanks for that, I have changed the code and it works great. I would
have liked to use the same form to create new records but got into all sorts
of bother when using me.combo14 = me.productID on the forms on current event.

Thanks again Dave

Phil


:

Since you are adding the record using a different form, you current form
doesn't know about it yet. You should requery your form in the Not In List
event after the record has been added in the add record form. Then rather
than a DLookup, use the FindFirst method on the form's recordsetclone to
navigate to the newly added record. Here is a modification of your current
code:

Private Sub Combo14_NotInList(NewData As String, Response As Integer)
Dim Msg As String

If NewData = "" Then
Exit Sub
End If

If MsgBox(NewData & "' is not in the list." & vbNewLine & vbNewLine & _
"Do you want to add it?", vbQuestion + vbYesNo) = vbYes Then
DoCmd.OpenForm "newpart", , , , acAdd, acDialog, NewData
Me.Combo14.Undo
DoCmd.OpenForm "newpart", , , , acAdd, acDialog, NewData
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClone
.FindFirst "[ProductID] = """ & NewData & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Else
Me.Combo14.Undo
Response = acDataErrContinue
End If

End Sub

Notice I used the constants vbQuestion, vbYesNo, etc rather than their
values. It makes the code much easier to read. Also, no need to Dim a
variable for a line feed. there are at least two different intrinsic
constants that do that. I prefer vbNewLine, because its purpose is obvious
to the reader.
--
Dave Hargis, Microsoft Access MVP


:

I am using a second form to enter new data, when I return back to the main
form I can only auto fill the productID detail, all the other data entered in
the 'newpart' form is not visible on the main form, Don't know how and where
to refresh the form data, can anyone assist? thankyou

Phil

Private Sub Combo14_AfterUpdate()

Set rs = Me.Recordset.Clone
rs.FindFirst "[productID] = '" & Me![Combo14] & "'"
Me.Bookmark = rs.Bookmark

End Sub

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

Dim Result
Dim Msg As String
Dim CR As String: CR = Chr$(13)
If NewData = "" Then Exit Sub
Msg = "'" & NewData & "' is not in the list." & CR & CR
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, 32 + 4) = 6 Then
DoCmd.OpenForm "newpart", , , , acAdd, acDialog, NewData
End If
Result = DLookup("[productID]", "products", _
"[productID]=""" & NewData & """")
If IsNull(Result) Then
Response = DATA_ERRCONTINUE
MsgBox "Please try again!"
Else
Response = DATA_ERRADDED
End If

End Sub

 

[Klatuu]

Use a bound text box to show the value rather than the combo. To reduce
confusion, I use a bound text box and put my unbound search combo in the form
header.
--
Dave Hargis, Microsoft Access MVP

I have got to keep the contents of the combo box visible otherwise I will not
know what record the fields on the form relate to!

Phil

Okay, that makes sense. My preference is to set it to Null so nothing shows
in it:
me.combo14 = Null
--
Dave Hargis, Microsoft Access MVP

Dave,

No that's correct, combo14 is not bound, that was partly the problem. When
scrolling through the records on the form, the contents of the combobox
didn't change, this was misleading and the advice provided was to use
me.combo14 = me.productID, this kept the combobox in sync with the record
that appeared on the screen. But for some reason this created a conflict when
I tried to use the same form to add records.

Phil

:

Why do you need to do that?
Combo14 is not a bound control, is it?
--
Dave Hargis, Microsoft Access MVP


:

Dave,

Many thanks for that, I have changed the code and it works great. I would
have liked to use the same form to create new records but got into all sorts
of bother when using me.combo14 = me.productID on the forms on current event.

Thanks again Dave

Phil


:

Since you are adding the record using a different form, you current form
doesn't know about it yet. You should requery your form in the Not In List
event after the record has been added in the add record form. Then rather
than a DLookup, use the FindFirst method on the form's recordsetclone to
navigate to the newly added record. Here is a modification of your current
code:

Private Sub Combo14_NotInList(NewData As String, Response As Integer)
Dim Msg As String

If NewData = "" Then
Exit Sub
End If

If MsgBox(NewData & "' is not in the list." & vbNewLine & vbNewLine & _
"Do you want to add it?", vbQuestion + vbYesNo) = vbYes Then
DoCmd.OpenForm "newpart", , , , acAdd, acDialog, NewData
Me.Combo14.Undo
DoCmd.OpenForm "newpart", , , , acAdd, acDialog, NewData
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClone
.FindFirst "[ProductID] = """ & NewData & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Else
Me.Combo14.Undo
Response = acDataErrContinue
End If

End Sub

Notice I used the constants vbQuestion, vbYesNo, etc rather than their
values. It makes the code much easier to read. Also, no need to Dim a
variable for a line feed. there are at least two different intrinsic
constants that do that. I prefer vbNewLine, because its purpose is obvious
to the reader.
--
Dave Hargis, Microsoft Access MVP


:

I am using a second form to enter new data, when I return back to the main
form I can only auto fill the productID detail, all the other data entered in
the 'newpart' form is not visible on the main form, Don't know how and where
to refresh the form data, can anyone assist? thankyou

Phil

Private Sub Combo14_AfterUpdate()

Set rs = Me.Recordset.Clone
rs.FindFirst "[productID] = '" & Me![Combo14] & "'"
Me.Bookmark = rs.Bookmark

End Sub

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

Dim Result
Dim Msg As String
Dim CR As String: CR = Chr$(13)
If NewData = "" Then Exit Sub
Msg = "'" & NewData & "' is not in the list." & CR & CR
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, 32 + 4) = 6 Then
DoCmd.OpenForm "newpart", , , , acAdd, acDialog, NewData
End If
Result = DLookup("[productID]", "products", _
"[productID]=""" & NewData & """")
If IsNull(Result) Then
Response = DATA_ERRCONTINUE
MsgBox "Please try again!"
Else
Response = DATA_ERRADDED
End If

End Sub

 

[gymphil]

Dave,

I hadn't thought of doing it that way.

I would much rather use the same form for entering new records but could you
please tell me what changes I would need to make to the coding, I know I do
not need to open the new form but not sure about the rest of it.

Many thanks

Phil

Use a bound text box to show the value rather than the combo. To reduce
confusion, I use a bound text box and put my unbound search combo in the form
header.
--
Dave Hargis, Microsoft Access MVP

I have got to keep the contents of the combo box visible otherwise I will not
know what record the fields on the form relate to!

Phil

Okay, that makes sense. My preference is to set it to Null so nothing shows
in it:
me.combo14 = Null
--
Dave Hargis, Microsoft Access MVP


:

Dave,

No that's correct, combo14 is not bound, that was partly the problem. When
scrolling through the records on the form, the contents of the combobox
didn't change, this was misleading and the advice provided was to use
me.combo14 = me.productID, this kept the combobox in sync with the record
that appeared on the screen. But for some reason this created a conflict when
I tried to use the same form to add records.

Phil

:

Why do you need to do that?
Combo14 is not a bound control, is it?
--
Dave Hargis, Microsoft Access MVP


:

Dave,

Many thanks for that, I have changed the code and it works great. I would
have liked to use the same form to create new records but got into all sorts
of bother when using me.combo14 = me.productID on the forms on current event.

Thanks again Dave

Phil


:

Since you are adding the record using a different form, you current form
doesn't know about it yet. You should requery your form in the Not In List
event after the record has been added in the add record form. Then rather
than a DLookup, use the FindFirst method on the form's recordsetclone to
navigate to the newly added record. Here is a modification of your current
code:

Private Sub Combo14_NotInList(NewData As String, Response As Integer)
Dim Msg As String

If NewData = "" Then
Exit Sub
End If

If MsgBox(NewData & "' is not in the list." & vbNewLine & vbNewLine & _
"Do you want to add it?", vbQuestion + vbYesNo) = vbYes Then
DoCmd.OpenForm "newpart", , , , acAdd, acDialog, NewData
Me.Combo14.Undo
DoCmd.OpenForm "newpart", , , , acAdd, acDialog, NewData
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClone
.FindFirst "[ProductID] = """ & NewData & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Else
Me.Combo14.Undo
Response = acDataErrContinue
End If

End Sub

Notice I used the constants vbQuestion, vbYesNo, etc rather than their
values. It makes the code much easier to read. Also, no need to Dim a
variable for a line feed. there are at least two different intrinsic
constants that do that. I prefer vbNewLine, because its purpose is obvious
to the reader.
--
Dave Hargis, Microsoft Access MVP


:

I am using a second form to enter new data, when I return back to the main
form I can only auto fill the productID detail, all the other data entered in
the 'newpart' form is not visible on the main form, Don't know how and where
to refresh the form data, can anyone assist? thankyou

Phil

Private Sub Combo14_AfterUpdate()

Set rs = Me.Recordset.Clone
rs.FindFirst "[productID] = '" & Me![Combo14] & "'"
Me.Bookmark = rs.Bookmark

End Sub

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

Dim Result
Dim Msg As String
Dim CR As String: CR = Chr$(13)
If NewData = "" Then Exit Sub
Msg = "'" & NewData & "' is not in the list." & CR & CR
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, 32 + 4) = 6 Then
DoCmd.OpenForm "newpart", , , , acAdd, acDialog, NewData
End If
Result = DLookup("[productID]", "products", _
"[productID]=""" & NewData & """")
If IsNull(Result) Then
Response = DATA_ERRCONTINUE
MsgBox "Please try again!"
Else
Response = DATA_ERRADDED
End If

End Sub

 

[Klatuu]

Just use the After Update event of the comb box to load the selected value
into the text box.
--
Dave Hargis, Microsoft Access MVP

Dave,

I hadn't thought of doing it that way.

I would much rather use the same form for entering new records but could you
please tell me what changes I would need to make to the coding, I know I do
not need to open the new form but not sure about the rest of it.

Many thanks

Phil

Use a bound text box to show the value rather than the combo. To reduce
confusion, I use a bound text box and put my unbound search combo in the form
header.
--
Dave Hargis, Microsoft Access MVP

I have got to keep the contents of the combo box visible otherwise I will not
know what record the fields on the form relate to!

Phil

:

Okay, that makes sense. My preference is to set it to Null so nothing shows
in it:
me.combo14 = Null
--
Dave Hargis, Microsoft Access MVP


:

Dave,

No that's correct, combo14 is not bound, that was partly the problem. When
scrolling through the records on the form, the contents of the combobox
didn't change, this was misleading and the advice provided was to use
me.combo14 = me.productID, this kept the combobox in sync with the record
that appeared on the screen. But for some reason this created a conflict when
I tried to use the same form to add records.

Phil

:

Why do you need to do that?
Combo14 is not a bound control, is it?
--
Dave Hargis, Microsoft Access MVP


:

Dave,

Many thanks for that, I have changed the code and it works great. I would
have liked to use the same form to create new records but got into all sorts
of bother when using me.combo14 = me.productID on the forms on current event.

Thanks again Dave

Phil


:

Since you are adding the record using a different form, you current form
doesn't know about it yet. You should requery your form in the Not In List
event after the record has been added in the add record form. Then rather
than a DLookup, use the FindFirst method on the form's recordsetclone to
navigate to the newly added record. Here is a modification of your current
code:

Private Sub Combo14_NotInList(NewData As String, Response As Integer)
Dim Msg As String

If NewData = "" Then
Exit Sub
End If

If MsgBox(NewData & "' is not in the list." & vbNewLine & vbNewLine & _
"Do you want to add it?", vbQuestion + vbYesNo) = vbYes Then
DoCmd.OpenForm "newpart", , , , acAdd, acDialog, NewData
Me.Combo14.Undo
DoCmd.OpenForm "newpart", , , , acAdd, acDialog, NewData
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClone
.FindFirst "[ProductID] = """ & NewData & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Else
Me.Combo14.Undo
Response = acDataErrContinue
End If

End Sub

Notice I used the constants vbQuestion, vbYesNo, etc rather than their
values. It makes the code much easier to read. Also, no need to Dim a
variable for a line feed. there are at least two different intrinsic
constants that do that. I prefer vbNewLine, because its purpose is obvious
to the reader.
--
Dave Hargis, Microsoft Access MVP


:

I am using a second form to enter new data, when I return back to the main
form I can only auto fill the productID detail, all the other data entered in
the 'newpart' form is not visible on the main form, Don't know how and where
to refresh the form data, can anyone assist? thankyou

Phil

Private Sub Combo14_AfterUpdate()

Set rs = Me.Recordset.Clone
rs.FindFirst "[productID] = '" & Me![Combo14] & "'"
Me.Bookmark = rs.Bookmark

End Sub

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

Dim Result
Dim Msg As String
Dim CR As String: CR = Chr$(13)
If NewData = "" Then Exit Sub
Msg = "'" & NewData & "' is not in the list." & CR & CR
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, 32 + 4) = 6 Then
DoCmd.OpenForm "newpart", , , , acAdd, acDialog, NewData
End If
Result = DLookup("[productID]", "products", _
"[productID]=""" & NewData & """")
If IsNull(Result) Then
Response = DATA_ERRCONTINUE
MsgBox "Please try again!"
Else
Response = DATA_ERRADDED
End If

End Sub