Erro when updating table via a listbox on a adp form.

[Rachel]

Hi,

Please can you help. I have added a listbox to a subform to enable the
user to select more than one option, I then want a table to be updated
with this infomation via a command button. ie this could mean
inserting several rows if more than one item is selected.

I have a form with the following controls:

A text box called txt_Case_ID
A listbox called lst_Diag (row source is from another table called
tbl_Medical)

There is no record source set for this form.

I have a table called tbl_Multi (to store the multiple entries) with
the following fields:

Multi_ID (this is the PK) (this is an auto number)
Medical_ID (this is a PK in another table - tbl_Medical) (I want this
to be updated by lst_Diag)
Case_ID (this is a PK in another table) (I want this to be updated by
txt_Case_ID)

I have added the following code to a command button:

Private Sub Command6_Click()

Dim strSQL As String
Dim varItem As Variant

For Each varItem In Me.lst_Diag.ItemsSelected
strSQL = "UPDATE [tbl_Multi] SET Case_ID = '" &
Replace(Me.txt_Case_ID, "'", "''") & "' WHERE Medical_ID = " &
Me.lst_Diag.ItemData(varItem)
CurrentDb.Execute strSQL
Next
Me.lst_Diag.Requery

End Sub

However I keep getting this error "Runtime error 91, with block
variable not set"

I have tried everything to get this to work but I keep failing.

Can anyone help me please?

Rachel

 

[Sylvain Lafontaine]

There is no CurrentDb object in ADP. Use the CurrentProject.Connection
object instead.

Before executing your code, you must also insure that all edited data in the
current (sub)form has been saved or you might encounter a data write
concurrency error.

 

[Rachel]

There is no CurrentDb object in ADP.  Use the CurrentProject.Connection
object instead.

Before executing your code, you must also insure that all edited data in the
current (sub)form has been saved or you might encounter a data write
concurrency error.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Hi,

Please can you help. I have added a listbox to a subform to enable the
user to select more than one option, I then want a table to be updated
with this infomation via a command button. ie this could mean
inserting several rows if more than one item is selected.

I have a form with the following controls:

A text box called txt_Case_ID
A listbox called lst_Diag (row source is from another table called
tbl_Medical)

There is no record source set for this form.

I have a table called tbl_Multi (to store the multiple entries) with
the following fields:

Multi_ID (this is the PK) (this is an auto number)
Medical_ID (this is a PK in another table - tbl_Medical) (I want this
to be updated by lst_Diag)
Case_ID (this is a PK in another table) (I want this to be updated by
txt_Case_ID)

I have added the following code to a command button:

Private Sub Command6_Click()

Dim strSQL As String
   Dim varItem As Variant

   For Each varItem In Me.lst_Diag.ItemsSelected
       strSQL = "UPDATE [tbl_Multi] SET Case_ID = '" &
Replace(Me.txt_Case_ID, "'", "''") & "' WHERE Medical_ID = " &
Me.lst_Diag.ItemData(varItem)
       CurrentDb.Execute strSQL
   Next
   Me.lst_Diag.Requery

End Sub

However I keep getting this error "Runtime error 91, with block
variable not set"

I have tried everything to get this to work but I keep failing.

Can anyone help me please?

Rachel- Hide quoted text -

- Show quoted text -

Thank you, I have amended my code as suggested and I now have no error
message. However, the table is still not populated. Any ideas?

Thanks in advance

 

[Sylvain Lafontaine]

Make sure that you code is correct by displaying the string strSQL and other
values to see what's going on.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

There is no CurrentDb object in ADP. Use the CurrentProject.Connection
object instead.

Before executing your code, you must also insure that all edited data in
the
current (sub)form has been saved or you might encounter a data write
concurrency error.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Hi,

Please can you help. I have added a listbox to a subform to enable the
user to select more than one option, I then want a table to be updated
with this infomation via a command button. ie this could mean
inserting several rows if more than one item is selected.

I have a form with the following controls:

A text box called txt_Case_ID
A listbox called lst_Diag (row source is from another table called
tbl_Medical)

There is no record source set for this form.

I have a table called tbl_Multi (to store the multiple entries) with
the following fields:

Multi_ID (this is the PK) (this is an auto number)
Medical_ID (this is a PK in another table - tbl_Medical) (I want this
to be updated by lst_Diag)
Case_ID (this is a PK in another table) (I want this to be updated by
txt_Case_ID)

I have added the following code to a command button:

Private Sub Command6_Click()

Dim strSQL As String
Dim varItem As Variant

For Each varItem In Me.lst_Diag.ItemsSelected
strSQL = "UPDATE [tbl_Multi] SET Case_ID = '" &
Replace(Me.txt_Case_ID, "'", "''") & "' WHERE Medical_ID = " &
Me.lst_Diag.ItemData(varItem)
CurrentDb.Execute strSQL
Next
Me.lst_Diag.Requery

End Sub

However I keep getting this error "Runtime error 91, with block
variable not set"

I have tried everything to get this to work but I keep failing.

Can anyone help me please?

Rachel- Hide quoted text -

- Show quoted text -

Thank you, I have amended my code as suggested and I now have no error
message. However, the table is still not populated. Any ideas?

Thanks in advance

 

[Rachel]

Make sure that you code is correct by displaying the string strSQL and other
values to see what's going on.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

There is no CurrentDb object in ADP. Use the CurrentProject.Connection
object instead.

Before executing your code, you must also insure that all edited data in
the
current (sub)form has been saved or you might encounter a data write
concurrency error.

news:5b65f8e0-d954-4139-8b1e-a8f6e406828f@m44g2000hsc.googlegroups.com....

Hi,
Please can you help. I have added a listbox to a subform to enable the
user to select more than one option, I then want a table to be updated
with this infomation via a command button. ie this could mean
inserting several rows if more than one item is selected.
I have a form with the following controls:
A text box called txt_Case_ID
A listbox called lst_Diag (row source is from another table called
tbl_Medical)
There is no record source set for this form.
I have a table called tbl_Multi (to store the multiple entries) with
the following fields:
Multi_ID (this is the PK) (this is an auto number)
Medical_ID (this is a PK in another table - tbl_Medical) (I want this
to be updated by lst_Diag)
Case_ID (this is a PK in another table) (I want this to be updated by
txt_Case_ID)
I have added the following code to a command button:
Private Sub Command6_Click()
Dim strSQL As String
Dim varItem As Variant
For Each varItem In Me.lst_Diag.ItemsSelected
strSQL = "UPDATE [tbl_Multi] SET Case_ID = '" &
Replace(Me.txt_Case_ID, "'", "''") & "' WHERE Medical_ID = " &
Me.lst_Diag.ItemData(varItem)
CurrentDb.Execute strSQL
Next
Me.lst_Diag.Requery
End Sub
However I keep getting this error "Runtime error 91, with block
variable not set"
I have tried everything to get this to work but I keep failing.
Can anyone help me please?
Rachel- Hide quoted text -

- Show quoted text -

Thank you, I have amended my code as suggested and I now have no error
message. However, the table is still not populated. Any ideas?

Thanks in advance- Hide quoted text -

- Show quoted text -

All working properly now, many thnaks for your help

Rachel