Using Loop to fill ONE of TEN text boxes.

[Guest]

Hello Everyone, may I be some advice please.

I have a database for our service department. I have a form for the repair
(RS_Results) and a form with the spare parts listed (Parts_List).

On the repair form I have 10 rows of text boxes like:

PartNo1 PartDescription1 PartPrice1
PartNo2 PartDescription2 PartPrice2
PartNo3 PartDescription3 PartPrice2
etc

On the parts list form I have a button next to each part to copy the details
back to the repair form using:

Forms!RS_results_edit.PartNo1 = Me.PartNo
Forms!RS_results_edit.PartDesc1 = Me.Description
Forms!RS_results_edit.PartPrice1 = Me.Retail

Manually this works but if I introduce the loop it fails.

I want to use a for next loop to find the first row of the 10 which has a
NULL value for PartNumber (therefore it's empty) and then paste in the
selected item.

I have tried:

Private Sub cmdCopyToRS_Click()
Dim x As Integer
'Check RS form for empty part number box.
For x = 1 To 10
If Not Forms!RS_results_edit.PartNo(x) = "" Then
'Copy the selected items to the first available parts row on the RS Form
Forms!RS_results_edit.PartNo(x) = Me.PartNo
'Forms!RS_results_edit.PartDesc1 = Me.Description 'Temporary commented out
'Forms!RS_results_edit.PartPrice1 = Me.Retail 'Temporary commented out
Next x
End Sub

But this throws debug error due to the (x) on the end of the field name.

So am I doing it right? If not what is the correct way to use a loop in this
way.

Is there a better way?

Hope someone can help. Thanks in advance...

--
Cheers
Chas

***************
* Spectrum is Green *
***************

 

[Nick via AccessMonster.com]

On the repair form I have 10 rows of text boxes like:

PartNo1 PartDescription1 PartPrice1
PartNo2 PartDescription2 PartPrice2
PartNo3 PartDescription3 PartPrice2
etc

I want to use a for next loop to find the first row of the 10 which has a
NULL value for PartNumber (therefore it's empty) and then paste in the
selected item.

I have tried:

Private Sub cmdCopyToRS_Click()
Dim x As Integer
'Check RS form for empty part number box.
For x = 1 To 10
If Not Forms!RS_results_edit.PartNo(x) = "" Then
'Copy the selected items to the first available parts row on the RS Form
Forms!RS_results_edit.PartNo(x) = Me.PartNo
'Forms!RS_results_edit.PartDesc1 = Me.Description 'Temporary commented out
'Forms!RS_results_edit.PartPrice1 = Me.Retail 'Temporary commented out
Next x
End Sub

Chas,

The problem is that you are trying to reference the PartNo control as an
array, which it is not. I worked around this same problem in my current
program by doing the following:

Replace 'Forms!RS_results_edit.PartNo(x)' with:

Forms("RS_results_edit")("PartNo" & x)

This will build the string value for your control, so it will appear as
"PartNo1" instead of "PartNo(1)"

Also, if you are checking for a null value, use:

If NotNz(Forms("RS_results_edit")("PartNo" & x),"") = ""

The Nz() function will test your PartNo control for a null value, and if null
will read it as a "" value. A null value is different than a "" (empty
string) value; I've had enough headaches caused by accidental Null values to
always use the Nz function.

Hope I answered your question,
Nick

 

[Guest]

Dear Nick,

Many thanks for the quick response. Your suggestions work well, thanks.

I have found that to look for an empty box I had to remove the NOT from the
test and it works OK, but now fills ALL the rows. I'll have to rework the
test to only fill the first "" it finds.

The ("PartNo" & x) works great, thanks

Thank you once again.
--
Cheers
Chas

***************
* Spectrum is Green *
***************