Not In List 2 values

  • Thread starter Thread starter CJ
  • Start date Start date
C

CJ

Hi Groupies:

The standard Not In List code that I use is something like:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String
strMsg = strMsg & "Add " & NewData & " to the list?"

If MsgBox(strMsg, vbQuestion + vbYesNo, "Item Not Available") = vbNo
Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("tblProducts", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!lngProductID = NewData
rs.Update

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

However, I need to know how to add 2 values to the table instead of just 1.
For example: I have lngProductID and strProductName that I would like to add
via VB.

Can somebody please help me out with this before I pull the rest of my hair
out?
 
<snip>

Set db = CurrentDb
Set rs = db.OpenRecordset("tblProducts", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!lngProductID = NewData

rs!strProductName = "Company Widget"

rs.Update

In other words, just use the recordset while you've got it open.
 
Hi CJ

Quite a common technique is to open a dialog form to enter the details for
the record which is to be added. So, in place of:

Set db = CurrentDb
Set rs = db.OpenRecordset("tblProducts", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!lngProductID = NewData
rs.Update

you have:

DoCmd.OpenForm "YourForm", WindowMode:=acDialog, OpenArgs:=NewData

Note that I am passing NewData to the form via OpenArgs. When your form
loads, it can use that to set the default value of the relevant field(s).

When your form closes, the code picks up where it left off, hopefully with
the new record having been created.

BTW, I notice you do not have any rs.Close in your code. Opening a
recordset and not closing it is one of the most common causes of Access
failing to close when you exit the application.
 
Hi Guys, thanks very much for popping in.

Stuart, I still get the same errors when I use your method, so I went with
Graham's.

Graham, the OpenArgs portion isn't doing what I wanted, meaning
it's not putting the data in from my current form. So, I have changed
what you had suggested to the following:

<snip>
DoCmd.OpenForm "frmProduct"
DoCmd.GoToRecord , , acNewRec
Me.lngProductID.Value = Forms!frmOrders.lngProductID
Me.strProduct.Value = Forms!frmOrders.strProduct
<snip>

However, although the form opens and sits on the new blank record,
no data is going in......which is what I wanted in the first place....

p.s. thanks for noticing the rs.close issue
--
Thanks for taking the time!

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
Graham Mandeno said:
Hi CJ

Quite a common technique is to open a dialog form to enter the details for
the record which is to be added. So, in place of:

Set db = CurrentDb
Set rs = db.OpenRecordset("tblProducts", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!lngProductID = NewData
rs.Update

you have:

DoCmd.OpenForm "YourForm", WindowMode:=acDialog, OpenArgs:=NewData

Note that I am passing NewData to the form via OpenArgs. When your form
loads, it can use that to set the default value of the relevant field(s).

When your form closes, the code picks up where it left off, hopefully with
the new record having been created.

BTW, I notice you do not have any rs.Close in your code. Opening a
recordset and not closing it is one of the most common causes of Access
failing to close when you exit the application.
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand
Click to expand...
 
Hi CJ

Where do lngProductID and strProduct come from?

And how are they related to NewData?

Note that OpenArgs doesn't do anything automatically. The value you pass is
available in your form as Me.OpenArgs. You have to explicitly do something
with it in your form's code. For example, you could use the form's Load
event to set a default value:

If Not IsNull(Me.OpenArgs) Then
Me.ProductName.DefaultValue = """" & Me.OpenArgs & """"
End If

I guess with a Products table there would be many fields such as
description, units, price, etc that would need to be filled in manually.
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

CJ said:
Hi Guys, thanks very much for popping in.

Stuart, I still get the same errors when I use your method, so I went with
Graham's.

Graham, the OpenArgs portion isn't doing what I wanted, meaning
it's not putting the data in from my current form. So, I have changed
what you had suggested to the following:

<snip>
DoCmd.OpenForm "frmProduct"
DoCmd.GoToRecord , , acNewRec
Me.lngProductID.Value = Forms!frmOrders.lngProductID
Me.strProduct.Value = Forms!frmOrders.strProduct
<snip>

However, although the form opens and sits on the new blank record,
no data is going in......which is what I wanted in the first place....

p.s. thanks for noticing the rs.close issue
--
Thanks for taking the time!

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
Graham Mandeno said:
Hi CJ

Quite a common technique is to open a dialog form to enter the details
for the record which is to be added. So, in place of:

Set db = CurrentDb
Set rs = db.OpenRecordset("tblProducts", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!lngProductID = NewData
rs.Update

you have:

DoCmd.OpenForm "YourForm", WindowMode:=acDialog, OpenArgs:=NewData

Note that I am passing NewData to the form via OpenArgs. When your form
loads, it can use that to set the default value of the relevant field(s).

When your form closes, the code picks up where it left off, hopefully
with the new record having been created.

BTW, I notice you do not have any rs.Close in your code. Opening a
recordset and not closing it is one of the most common causes of Access
failing to close when you exit the application.
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

CJ said:
Hi Groupies:

The standard Not In List code that I use is something like:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String
strMsg = strMsg & "Add " & NewData & " to the list?"

If MsgBox(strMsg, vbQuestion + vbYesNo, "Item Not Available") = vbNo
Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("tblProducts", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!lngProductID = NewData
rs.Update

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

However, I need to know how to add 2 values to the table instead of just
1.
For example: I have lngProductID and strProductName that I would like to
add via VB.

Can somebody please help me out with this before I pull the rest of my
hair out?
Click to expand...
Click to expand...
Click to expand...
 
Not a problem, Graham.

You steered me in the right direction and I think I will be able to solve
it.

--
Thanks for taking the time!

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
Graham Mandeno said:
Hi CJ

Where do lngProductID and strProduct come from?

And how are they related to NewData?

Note that OpenArgs doesn't do anything automatically. The value you pass
is available in your form as Me.OpenArgs. You have to explicitly do
something with it in your form's code. For example, you could use the
form's Load event to set a default value:

If Not IsNull(Me.OpenArgs) Then
Me.ProductName.DefaultValue = """" & Me.OpenArgs & """"
End If

I guess with a Products table there would be many fields such as
description, units, price, etc that would need to be filled in manually.
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

CJ said:
Hi Guys, thanks very much for popping in.

Stuart, I still get the same errors when I use your method, so I went
with Graham's.

Graham, the OpenArgs portion isn't doing what I wanted, meaning
it's not putting the data in from my current form. So, I have changed
what you had suggested to the following:

<snip>
DoCmd.OpenForm "frmProduct"
DoCmd.GoToRecord , , acNewRec
Me.lngProductID.Value = Forms!frmOrders.lngProductID
Me.strProduct.Value = Forms!frmOrders.strProduct
<snip>

However, although the form opens and sits on the new blank record,
no data is going in......which is what I wanted in the first place....

p.s. thanks for noticing the rs.close issue
--
Thanks for taking the time!

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
Graham Mandeno said:
Hi CJ

Quite a common technique is to open a dialog form to enter the details
for the record which is to be added. So, in place of:

Set db = CurrentDb
Set rs = db.OpenRecordset("tblProducts", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!lngProductID = NewData
rs.Update

you have:

DoCmd.OpenForm "YourForm", WindowMode:=acDialog, OpenArgs:=NewData

Note that I am passing NewData to the form via OpenArgs. When your form
loads, it can use that to set the default value of the relevant
field(s).

When your form closes, the code picks up where it left off, hopefully
with the new record having been created.

BTW, I notice you do not have any rs.Close in your code. Opening a
recordset and not closing it is one of the most common causes of Access
failing to close when you exit the application.
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Groupies:

The standard Not In List code that I use is something like:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String
strMsg = strMsg & "Add " & NewData & " to the list?"

If MsgBox(strMsg, vbQuestion + vbYesNo, "Item Not Available") = vbNo
Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("tblProducts", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!lngProductID = NewData
rs.Update

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

However, I need to know how to add 2 values to the table instead of
just 1.
For example: I have lngProductID and strProductName that I would like
to add via VB.

Can somebody please help me out with this before I pull the rest of my
hair out?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top