Auto fill value in a column

  • Thread starter Thread starter ChoonBoy
  • Start date Start date
C

ChoonBoy

I am wondering if this is possible and appreciate any guide here.

I have a column (Scanned data) with incomplete values. Eg Row1 value =
"INVnos1" and Row2 until Row13 = Null. Row14 value = "INVnos2" and Row15
until Row30 = Null and so on.

How do I use codes / macro to fill Row2 until Row13 with value "INVnos1" and
Row15 until Row30 with value "INVnos2".

Thanks in advance.
 
Do you have some way to order the records so you know they are in the correct
order to get the correct value? If so, you can use a loop like this to do
it. You can't count on row numbers or a specific order. I suggest you use a
query to order the records, then use a recordset based on that query to add
the values.

Dim rst As DAO.Recordset
Dim strCurrVal As String

Set rst = Currentdb.OpenRecordset(qryMyQuery, dbOpenDynaset)

If rst.RecordCount = 0 Then
Msgbox "No Records to Process"
Exit Sub
End If

With rst
.MoveLast
.MoveFirst
If IsNull(![Scanned Data]) Then
MsgBox "First Row Contains No Data"
Else
strCurrVal = ![Scanned Data]
Do While Not .EOF
If IsNull(![Scanned Data]) Then
.Edit
![Scanned Data] = strCurrVal
.Update
Else
strCurrVal = ![Scanned Data]
EndIf
.MoveNext
Loop
End If
.Close
End With

Set rst = Nothing

Note, this is untested "air code" and may need debugging, but it will give
you an idea.
 
Thanks for the response, really appreciate this.

I copied and placed the codes behind a button.

When I run it, Error 3078 with Msg MS Jet database cannot find input table
or query. Make sure it exist.

I have the query renamed to qryMyQuery but still the same message.

Is there anything I have missed out.

Klatuu said:
Do you have some way to order the records so you know they are in the correct
order to get the correct value? If so, you can use a loop like this to do
it. You can't count on row numbers or a specific order. I suggest you use a
query to order the records, then use a recordset based on that query to add
the values.

Dim rst As DAO.Recordset
Dim strCurrVal As String

Set rst = Currentdb.OpenRecordset(qryMyQuery, dbOpenDynaset)

If rst.RecordCount = 0 Then
Msgbox "No Records to Process"
Exit Sub
End If

With rst
.MoveLast
.MoveFirst
If IsNull(![Scanned Data]) Then
MsgBox "First Row Contains No Data"
Else
strCurrVal = ![Scanned Data]
Do While Not .EOF
If IsNull(![Scanned Data]) Then
.Edit
![Scanned Data] = strCurrVal
.Update
Else
strCurrVal = ![Scanned Data]
EndIf
.MoveNext
Loop
End If
.Close
End With

Set rst = Nothing

Note, this is untested "air code" and may need debugging, but it will give
you an idea.
--
Dave Hargis, Microsoft Access MVP


ChoonBoy said:
I am wondering if this is possible and appreciate any guide here.

I have a column (Scanned data) with incomplete values. Eg Row1 value =
"INVnos1" and Row2 until Row13 = Null. Row14 value = "INVnos2" and Row15
until Row30 = Null and so on.

How do I use codes / macro to fill Row2 until Row13 with value "INVnos1" and
Row15 until Row30 with value "INVnos2".

Thanks in advance.
Click to expand...
Click to expand...
 
As I said, it was untested.
Can you tell my how you are using the query?
It would be helpful if you posted the code where you use it.
--
Dave Hargis, Microsoft Access MVP


ChoonBoy said:
Thanks for the response, really appreciate this.

I copied and placed the codes behind a button.

When I run it, Error 3078 with Msg MS Jet database cannot find input table
or query. Make sure it exist.

I have the query renamed to qryMyQuery but still the same message.

Is there anything I have missed out.

Klatuu said:
Do you have some way to order the records so you know they are in the correct
order to get the correct value? If so, you can use a loop like this to do
it. You can't count on row numbers or a specific order. I suggest you use a
query to order the records, then use a recordset based on that query to add
the values.

Dim rst As DAO.Recordset
Dim strCurrVal As String

Set rst = Currentdb.OpenRecordset(qryMyQuery, dbOpenDynaset)

If rst.RecordCount = 0 Then
Msgbox "No Records to Process"
Exit Sub
End If

With rst
.MoveLast
.MoveFirst
If IsNull(![Scanned Data]) Then
MsgBox "First Row Contains No Data"
Else
strCurrVal = ![Scanned Data]
Do While Not .EOF
If IsNull(![Scanned Data]) Then
.Edit
![Scanned Data] = strCurrVal
.Update
Else
strCurrVal = ![Scanned Data]
EndIf
.MoveNext
Loop
End If
.Close
End With

Set rst = Nothing

Note, this is untested "air code" and may need debugging, but it will give
you an idea.
--
Dave Hargis, Microsoft Access MVP


ChoonBoy said:
I am wondering if this is possible and appreciate any guide here.

I have a column (Scanned data) with incomplete values. Eg Row1 value =
"INVnos1" and Row2 until Row13 = Null. Row14 value = "INVnos2" and Row15
until Row30 = Null and so on.

How do I use codes / macro to fill Row2 until Row13 with value "INVnos1" and
Row15 until Row30 with value "INVnos2".

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Are you sure you satated the row numbers correctly?
You have 13 rows for INVnos1 and 16 for INVnos2.
 
Actually it is hard to put in words but is something like this.

I am using a simple table with an auto number field (incremental) and a text
field.

1) A scanner is used to scan invoice nos to the text field (all invoices
starts with INVxxx
2) After the invoice nos is scanned, it is followed by scanning the products
into the same field but the next row (all products codes starts with either
EGxxxxx or AGxxxxx)
3) So you will have something like this for the column InvNos
/Prd1/Prd2/Prd3 etc. Next InvNos /Prd1/Prd2/Prd3/Prd4 etc, and the list goes
on.
4) I want to create a report to show each InvNos (group header) with Prds in
Detail section)
5) I am not able to do the report if I cannot associate the respective
InvNos to the Prds on the next column.
6) Using expression in the query (Scanned Data: IIf([barcode] Like
"inv*",[barcode],""), I am only able to move the InvNos to the next column.
7) The cell next to the prds is blank (which should be the respective InvNos.)

I hope I manage to give you a better picture.

Thanks
 
Do you have multiple invoices in the same record?
If that is true then you need a two step process. First to put each invoice
in separate records with the product information.

Post your table and field names and sample data - maybe two records.

--
Build a little, test a little.


ChoonBoy said:
Actually it is hard to put in words but is something like this.

I am using a simple table with an auto number field (incremental) and a text
field.

1) A scanner is used to scan invoice nos to the text field (all invoices
starts with INVxxx
2) After the invoice nos is scanned, it is followed by scanning the products
into the same field but the next row (all products codes starts with either
EGxxxxx or AGxxxxx)
3) So you will have something like this for the column InvNos
/Prd1/Prd2/Prd3 etc. Next InvNos /Prd1/Prd2/Prd3/Prd4 etc, and the list goes
on.
4) I want to create a report to show each InvNos (group header) with Prds in
Detail section)
5) I am not able to do the report if I cannot associate the respective
InvNos to the Prds on the next column.
6) Using expression in the query (Scanned Data: IIf([barcode] Like
"inv*",[barcode],""), I am only able to move the InvNos to the next column.
7) The cell next to the prds is blank (which should be the respective InvNos.)

I hope I manage to give you a better picture.

Thanks



KARL DEWEY said:
Are you sure you satated the row numbers correctly?
You have 13 rows for INVnos1 and 16 for INVnos2.
Click to expand...
Click to expand...
 
I do not have multiple invoices in the same record but I have an Invoice with
multiple products.

- First we scan the Invoice barcode which is captured in the text field.
- After this we scan the invoiced products barcode
- When the first Invoice + products are scanned, we continue with the other
invoices and products.

All the data are captured in one column, starting with Invoice code and
followed by product code in the next row.

1) My Table (Tbl_Input) only have 2 fields "RowNum" (Auto number) and
"Scanned Data" (text)

2) I use a datasheet Form (disabled sorting) with datasource pointing to the
Tbl_Input.

3) Any row of codes below the Invoice code are products associated to the
invoice until the next invoice number is encountered.

4) I need to build a report which must show "Invoice Code" as group header
and its products in the detail section.

5) I am not able to build the report with the only one column of mix codes.
I must somehow create another column so that it will show product's code in
one column and Invoice code in the next.

6) I do not know how to do No5 and will appreciate any help here.

Thank in advance.

KARL DEWEY said:
Do you have multiple invoices in the same record?
If that is true then you need a two step process. First to put each invoice
in separate records with the product information.

Post your table and field names and sample data - maybe two records.

--
Build a little, test a little.


ChoonBoy said:
Actually it is hard to put in words but is something like this.

I am using a simple table with an auto number field (incremental) and a text
field.

1) A scanner is used to scan invoice nos to the text field (all invoices
starts with INVxxx
2) After the invoice nos is scanned, it is followed by scanning the products
into the same field but the next row (all products codes starts with either
EGxxxxx or AGxxxxx)
3) So you will have something like this for the column InvNos
/Prd1/Prd2/Prd3 etc. Next InvNos /Prd1/Prd2/Prd3/Prd4 etc, and the list goes
on.
4) I want to create a report to show each InvNos (group header) with Prds in
Detail section)
5) I am not able to do the report if I cannot associate the respective
InvNos to the Prds on the next column.
6) Using expression in the query (Scanned Data: IIf([barcode] Like
"inv*",[barcode],""), I am only able to move the InvNos to the next column.
7) The cell next to the prds is blank (which should be the respective InvNos.)

I hope I manage to give you a better picture.

Thanks



KARL DEWEY said:
Are you sure you satated the row numbers correctly?
You have 13 rows for INVnos1 and 16 for INVnos2.

--
Build a little, test a little.


:

I am wondering if this is possible and appreciate any guide here.

I have a column (Scanned data) with incomplete values. Eg Row1 value =
"INVnos1" and Row2 until Row13 = Null. Row14 value = "INVnos2" and Row15
until Row30 = Null and so on.

How do I use codes / macro to fill Row2 until Row13 with value "INVnos1" and
Row15 until Row30 with value "INVnos2".

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
I assume your data looks like this --
RowNum Scanned Data
1 INV123
AG67891
AG67895
EG12344
EG12345
2 INV456
AG67891
AG67895
AG97895
EG12344
EG12345
having a Chr(13) & Chr(10) between invoice and products.
Create a table like this --
ROWNUM INVOICE PRODUCT
Long integer Text Text

Build these three queries --

ChoonBoy_1
INSERT INTO INV_PROD ( ROWNUM, INVOICE, PRODUCT )
SELECT Tbl_Input.RowNum, Left([Scanned Data],6) AS Expr1, Mid([Scanned
Data],InStr([Scanned Data],"EG"),7) AS Expr2
FROM Tbl_Input;

ChoonBoy_2
INSERT INTO INV_PROD ( ROWNUM, INVOICE, PRODUCT )
SELECT Tbl_Input.RowNum, Left([Scanned Data],6) AS Expr1, Mid([Scanned
Data],InStr([Scanned Data],"AG"),7) AS Expr2
FROM Tbl_Input;

ChoonBoy_3
UPDATE INV_PROD, Tbl_Input SET Tbl_Input.[Scanned Data] =
Replace(Replace([Scanned Data],[PRODUCT] & Chr(13) & Chr(10),""),[PRODUCT] &
Chr(13) & Chr(10),"")
WHERE (((Left([Scanned Data],6))=[INVOICE]));

Create a macro and Set Warning - No. Add these thre queries using action
OpenQuery as many times as you will have products on an invoice. And finish
macro with Set Warning - Yes.

The first two queries will append invoice and product to the Inv_Prod table,
one product at a time. Third query will remove the product and the carriage
return & line feed. When it is done there will be one product left with the
incoice number as the record will not have a carriage return & line feed on
the end of the data.

--
Build a little, test a little.


ChoonBoy said:
I do not have multiple invoices in the same record but I have an Invoice with
multiple products.

- First we scan the Invoice barcode which is captured in the text field.
- After this we scan the invoiced products barcode
- When the first Invoice + products are scanned, we continue with the other
invoices and products.

All the data are captured in one column, starting with Invoice code and
followed by product code in the next row.

1) My Table (Tbl_Input) only have 2 fields "RowNum" (Auto number) and
"Scanned Data" (text)

2) I use a datasheet Form (disabled sorting) with datasource pointing to the
Tbl_Input.

3) Any row of codes below the Invoice code are products associated to the
invoice until the next invoice number is encountered.

4) I need to build a report which must show "Invoice Code" as group header
and its products in the detail section.

5) I am not able to build the report with the only one column of mix codes.
I must somehow create another column so that it will show product's code in
one column and Invoice code in the next.

6) I do not know how to do No5 and will appreciate any help here.

Thank in advance.

KARL DEWEY said:
Do you have multiple invoices in the same record?
If that is true then you need a two step process. First to put each invoice
in separate records with the product information.

Post your table and field names and sample data - maybe two records.

--
Build a little, test a little.


ChoonBoy said:
Actually it is hard to put in words but is something like this.

I am using a simple table with an auto number field (incremental) and a text
field.

1) A scanner is used to scan invoice nos to the text field (all invoices
starts with INVxxx
2) After the invoice nos is scanned, it is followed by scanning the products
into the same field but the next row (all products codes starts with either
EGxxxxx or AGxxxxx)
3) So you will have something like this for the column InvNos
/Prd1/Prd2/Prd3 etc. Next InvNos /Prd1/Prd2/Prd3/Prd4 etc, and the list goes
on.
4) I want to create a report to show each InvNos (group header) with Prds in
Detail section)
5) I am not able to do the report if I cannot associate the respective
InvNos to the Prds on the next column.
6) Using expression in the query (Scanned Data: IIf([barcode] Like
"inv*",[barcode],""), I am only able to move the InvNos to the next column.
7) The cell next to the prds is blank (which should be the respective InvNos.)

I hope I manage to give you a better picture.

Thanks



:

Are you sure you satated the row numbers correctly?
You have 13 rows for INVnos1 and 16 for INVnos2.

--
Build a little, test a little.


:

I am wondering if this is possible and appreciate any guide here.

I have a column (Scanned data) with incomplete values. Eg Row1 value =
"INVnos1" and Row2 until Row13 = Null. Row14 value = "INVnos2" and Row15
until Row30 = Null and so on.

How do I use codes / macro to fill Row2 until Row13 with value "INVnos1" and
Row15 until Row30 with value "INVnos2".

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Forgot one thing.

Make a primary key of the three fields - ROWNUM, INVOICE, and PRODUCT.

--
Build a little, test a little.


KARL DEWEY said:
I assume your data looks like this --
RowNum Scanned Data
1 INV123
AG67891
AG67895
EG12344
EG12345
2 INV456
AG67891
AG67895
AG97895
EG12344
EG12345
having a Chr(13) & Chr(10) between invoice and products.
Create a table like this --
ROWNUM INVOICE PRODUCT
Long integer Text Text

Build these three queries --

ChoonBoy_1
INSERT INTO INV_PROD ( ROWNUM, INVOICE, PRODUCT )
SELECT Tbl_Input.RowNum, Left([Scanned Data],6) AS Expr1, Mid([Scanned
Data],InStr([Scanned Data],"EG"),7) AS Expr2
FROM Tbl_Input;

ChoonBoy_2
INSERT INTO INV_PROD ( ROWNUM, INVOICE, PRODUCT )
SELECT Tbl_Input.RowNum, Left([Scanned Data],6) AS Expr1, Mid([Scanned
Data],InStr([Scanned Data],"AG"),7) AS Expr2
FROM Tbl_Input;

ChoonBoy_3
UPDATE INV_PROD, Tbl_Input SET Tbl_Input.[Scanned Data] =
Replace(Replace([Scanned Data],[PRODUCT] & Chr(13) & Chr(10),""),[PRODUCT] &
Chr(13) & Chr(10),"")
WHERE (((Left([Scanned Data],6))=[INVOICE]));

Create a macro and Set Warning - No. Add these thre queries using action
OpenQuery as many times as you will have products on an invoice. And finish
macro with Set Warning - Yes.

The first two queries will append invoice and product to the Inv_Prod table,
one product at a time. Third query will remove the product and the carriage
return & line feed. When it is done there will be one product left with the
incoice number as the record will not have a carriage return & line feed on
the end of the data.

--
Build a little, test a little.


ChoonBoy said:
I do not have multiple invoices in the same record but I have an Invoice with
multiple products.

- First we scan the Invoice barcode which is captured in the text field.
- After this we scan the invoiced products barcode
- When the first Invoice + products are scanned, we continue with the other
invoices and products.

All the data are captured in one column, starting with Invoice code and
followed by product code in the next row.

1) My Table (Tbl_Input) only have 2 fields "RowNum" (Auto number) and
"Scanned Data" (text)

2) I use a datasheet Form (disabled sorting) with datasource pointing to the
Tbl_Input.

3) Any row of codes below the Invoice code are products associated to the
invoice until the next invoice number is encountered.

4) I need to build a report which must show "Invoice Code" as group header
and its products in the detail section.

5) I am not able to build the report with the only one column of mix codes.
I must somehow create another column so that it will show product's code in
one column and Invoice code in the next.

6) I do not know how to do No5 and will appreciate any help here.

Thank in advance.

KARL DEWEY said:
Do you have multiple invoices in the same record?
If that is true then you need a two step process. First to put each invoice
in separate records with the product information.

Post your table and field names and sample data - maybe two records.

--
Build a little, test a little.


:

Actually it is hard to put in words but is something like this.

I am using a simple table with an auto number field (incremental) and a text
field.

1) A scanner is used to scan invoice nos to the text field (all invoices
starts with INVxxx
2) After the invoice nos is scanned, it is followed by scanning the products
into the same field but the next row (all products codes starts with either
EGxxxxx or AGxxxxx)
3) So you will have something like this for the column InvNos
/Prd1/Prd2/Prd3 etc. Next InvNos /Prd1/Prd2/Prd3/Prd4 etc, and the list goes
on.
4) I want to create a report to show each InvNos (group header) with Prds in
Detail section)
5) I am not able to do the report if I cannot associate the respective
InvNos to the Prds on the next column.
6) Using expression in the query (Scanned Data: IIf([barcode] Like
"inv*",[barcode],""), I am only able to move the InvNos to the next column.
7) The cell next to the prds is blank (which should be the respective InvNos.)

I hope I manage to give you a better picture.

Thanks



:

Are you sure you satated the row numbers correctly?
You have 13 rows for INVnos1 and 16 for INVnos2.

--
Build a little, test a little.


:

I am wondering if this is possible and appreciate any guide here.

I have a column (Scanned data) with incomplete values. Eg Row1 value =
"INVnos1" and Row2 until Row13 = Null. Row14 value = "INVnos2" and Row15
until Row30 = Null and so on.

How do I use codes / macro to fill Row2 until Row13 with value "INVnos1" and
Row15 until Row30 with value "INVnos2".

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Hi Karl,

Thank you for the effort and help. Some how, the result is not what I
expected. I must have missed a step or two.

Your assumption is correct, My data looks like this

rownum scanned data
1 INV12345
2 AG67891
3 AG67895
4 EG12344
5 EG12345
6 INV45666
7 AG67891
8 AG67895
9 AG97895
10 EG12344
11 EG12345

Note that Row 1 and Row 6 are Invoice Nos. Row 2 -5 and 7 - 8 are products
listed for the respective invoices

I want to generate this from the above data (either query or table)

Rownum Product Invoice
2 AG67891 INV12345
3 AG67895 INV12345
4 EG12344 INV12345
5 EG12345 INV12345
7 AG67891 INV45666
8 AG67895 INV45666
9 AG97895 INV45666
10 EG12344 INV45666
11 EG12345 INV45666

Thanks again.



KARL DEWEY said:
I assume your data looks like this --
RowNum Scanned Data
1 INV123
AG67891
AG67895
EG12344
EG12345
2 INV456
AG67891
AG67895
AG97895
EG12344
EG12345
having a Chr(13) & Chr(10) between invoice and products.
Create a table like this --
ROWNUM INVOICE PRODUCT
Long integer Text Text

Build these three queries --

ChoonBoy_1
INSERT INTO INV_PROD ( ROWNUM, INVOICE, PRODUCT )
SELECT Tbl_Input.RowNum, Left([Scanned Data],6) AS Expr1, Mid([Scanned
Data],InStr([Scanned Data],"EG"),7) AS Expr2
FROM Tbl_Input;

ChoonBoy_2
INSERT INTO INV_PROD ( ROWNUM, INVOICE, PRODUCT )
SELECT Tbl_Input.RowNum, Left([Scanned Data],6) AS Expr1, Mid([Scanned
Data],InStr([Scanned Data],"AG"),7) AS Expr2
FROM Tbl_Input;

ChoonBoy_3
UPDATE INV_PROD, Tbl_Input SET Tbl_Input.[Scanned Data] =
Replace(Replace([Scanned Data],[PRODUCT] & Chr(13) & Chr(10),""),[PRODUCT] &
Chr(13) & Chr(10),"")
WHERE (((Left([Scanned Data],6))=[INVOICE]));

Create a macro and Set Warning - No. Add these thre queries using action
OpenQuery as many times as you will have products on an invoice. And finish
macro with Set Warning - Yes.

The first two queries will append invoice and product to the Inv_Prod table,
one product at a time. Third query will remove the product and the carriage
return & line feed. When it is done there will be one product left with the
incoice number as the record will not have a carriage return & line feed on
the end of the data.

--
Build a little, test a little.


ChoonBoy said:
I do not have multiple invoices in the same record but I have an Invoice with
multiple products.

- First we scan the Invoice barcode which is captured in the text field.
- After this we scan the invoiced products barcode
- When the first Invoice + products are scanned, we continue with the other
invoices and products.

All the data are captured in one column, starting with Invoice code and
followed by product code in the next row.

1) My Table (Tbl_Input) only have 2 fields "RowNum" (Auto number) and
"Scanned Data" (text)

2) I use a datasheet Form (disabled sorting) with datasource pointing to the
Tbl_Input.

3) Any row of codes below the Invoice code are products associated to the
invoice until the next invoice number is encountered.

4) I need to build a report which must show "Invoice Code" as group header
and its products in the detail section.

5) I am not able to build the report with the only one column of mix codes.
I must somehow create another column so that it will show product's code in
one column and Invoice code in the next.

6) I do not know how to do No5 and will appreciate any help here.

Thank in advance.

KARL DEWEY said:
Do you have multiple invoices in the same record?
If that is true then you need a two step process. First to put each invoice
in separate records with the product information.

Post your table and field names and sample data - maybe two records.

--
Build a little, test a little.


:

Actually it is hard to put in words but is something like this.

I am using a simple table with an auto number field (incremental) and a text
field.

1) A scanner is used to scan invoice nos to the text field (all invoices
starts with INVxxx
2) After the invoice nos is scanned, it is followed by scanning the products
into the same field but the next row (all products codes starts with either
EGxxxxx or AGxxxxx)
3) So you will have something like this for the column InvNos
/Prd1/Prd2/Prd3 etc. Next InvNos /Prd1/Prd2/Prd3/Prd4 etc, and the list goes
on.
4) I want to create a report to show each InvNos (group header) with Prds in
Detail section)
5) I am not able to do the report if I cannot associate the respective
InvNos to the Prds on the next column.
6) Using expression in the query (Scanned Data: IIf([barcode] Like
"inv*",[barcode],""), I am only able to move the InvNos to the next column.
7) The cell next to the prds is blank (which should be the respective InvNos.)

I hope I manage to give you a better picture.

Thanks



:

Are you sure you satated the row numbers correctly?
You have 13 rows for INVnos1 and 16 for INVnos2.

--
Build a little, test a little.


:

I am wondering if this is possible and appreciate any guide here.

I have a column (Scanned data) with incomplete values. Eg Row1 value =
"INVnos1" and Row2 until Row13 = Null. Row14 value = "INVnos2" and Row15
until Row30 = Null and so on.

How do I use codes / macro to fill Row2 until Row13 with value "INVnos1" and
Row15 until Row30 with value "INVnos2".

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Hi Karl,

Thank you I got it now. After much looking around in the Internet, I was
linked back to your post dated 11/14/2008 in this
microsoft.public.access.formscoding.

With some modification, it worked great.

It would have taken me forever if not for your help.

Regards

KARL DEWEY said:
Forgot one thing.

Make a primary key of the three fields - ROWNUM, INVOICE, and PRODUCT.

--
Build a little, test a little.


KARL DEWEY said:
I assume your data looks like this --
RowNum Scanned Data
1 INV123
AG67891
AG67895
EG12344
EG12345
2 INV456
AG67891
AG67895
AG97895
EG12344
EG12345
having a Chr(13) & Chr(10) between invoice and products.
Create a table like this --
ROWNUM INVOICE PRODUCT
Long integer Text Text

Build these three queries --

ChoonBoy_1
INSERT INTO INV_PROD ( ROWNUM, INVOICE, PRODUCT )
SELECT Tbl_Input.RowNum, Left([Scanned Data],6) AS Expr1, Mid([Scanned
Data],InStr([Scanned Data],"EG"),7) AS Expr2
FROM Tbl_Input;

ChoonBoy_2
INSERT INTO INV_PROD ( ROWNUM, INVOICE, PRODUCT )
SELECT Tbl_Input.RowNum, Left([Scanned Data],6) AS Expr1, Mid([Scanned
Data],InStr([Scanned Data],"AG"),7) AS Expr2
FROM Tbl_Input;

ChoonBoy_3
UPDATE INV_PROD, Tbl_Input SET Tbl_Input.[Scanned Data] =
Replace(Replace([Scanned Data],[PRODUCT] & Chr(13) & Chr(10),""),[PRODUCT] &
Chr(13) & Chr(10),"")
WHERE (((Left([Scanned Data],6))=[INVOICE]));

Create a macro and Set Warning - No. Add these thre queries using action
OpenQuery as many times as you will have products on an invoice. And finish
macro with Set Warning - Yes.

The first two queries will append invoice and product to the Inv_Prod table,
one product at a time. Third query will remove the product and the carriage
return & line feed. When it is done there will be one product left with the
incoice number as the record will not have a carriage return & line feed on
the end of the data.

--
Build a little, test a little.


ChoonBoy said:
I do not have multiple invoices in the same record but I have an Invoice with
multiple products.

- First we scan the Invoice barcode which is captured in the text field.
- After this we scan the invoiced products barcode
- When the first Invoice + products are scanned, we continue with the other
invoices and products.

All the data are captured in one column, starting with Invoice code and
followed by product code in the next row.

1) My Table (Tbl_Input) only have 2 fields "RowNum" (Auto number) and
"Scanned Data" (text)

2) I use a datasheet Form (disabled sorting) with datasource pointing to the
Tbl_Input.

3) Any row of codes below the Invoice code are products associated to the
invoice until the next invoice number is encountered.

4) I need to build a report which must show "Invoice Code" as group header
and its products in the detail section.

5) I am not able to build the report with the only one column of mix codes.
I must somehow create another column so that it will show product's code in
one column and Invoice code in the next.

6) I do not know how to do No5 and will appreciate any help here.

Thank in advance.

:

Do you have multiple invoices in the same record?
If that is true then you need a two step process. First to put each invoice
in separate records with the product information.

Post your table and field names and sample data - maybe two records.

--
Build a little, test a little.


:

Actually it is hard to put in words but is something like this.

I am using a simple table with an auto number field (incremental) and a text
field.

1) A scanner is used to scan invoice nos to the text field (all invoices
starts with INVxxx
2) After the invoice nos is scanned, it is followed by scanning the products
into the same field but the next row (all products codes starts with either
EGxxxxx or AGxxxxx)
3) So you will have something like this for the column InvNos
/Prd1/Prd2/Prd3 etc. Next InvNos /Prd1/Prd2/Prd3/Prd4 etc, and the list goes
on.
4) I want to create a report to show each InvNos (group header) with Prds in
Detail section)
5) I am not able to do the report if I cannot associate the respective
InvNos to the Prds on the next column.
6) Using expression in the query (Scanned Data: IIf([barcode] Like
"inv*",[barcode],""), I am only able to move the InvNos to the next column.
7) The cell next to the prds is blank (which should be the respective InvNos.)

I hope I manage to give you a better picture.

Thanks



:

Are you sure you satated the row numbers correctly?
You have 13 rows for INVnos1 and 16 for INVnos2.

--
Build a little, test a little.


:

I am wondering if this is possible and appreciate any guide here.

I have a column (Scanned data) with incomplete values. Eg Row1 value =
"INVnos1" and Row2 until Row13 = Null. Row14 value = "INVnos2" and Row15
until Row30 = Null and so on.

How do I use codes / macro to fill Row2 until Row13 with value "INVnos1" and
Row15 until Row30 with value "INVnos2".

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Same table as posted earlier but make a primary key of the two fields -
INVOICE, and PRODUCT.

Use the below four queries once, each following the other.
ChoonBoy_1
INSERT INTO Inv_Prod ( RowNum, Invoice, Product )
SELECT [ChoonBoy].[RowNum]+1 AS Expr1, ChoonBoy.[Scanned Data],
ChoonBoy_1.[Scanned Data]
FROM ChoonBoy, ChoonBoy AS ChoonBoy_1
WHERE (((ChoonBoy.[Scanned Data]) Like "INV*") AND
((ChoonBoy_1.RowNum)=[ChoonBoy].[RowNum]+1));

ChoonBoy_2
UPDATE Inv_Prod INNER JOIN ChoonBoy ON Inv_Prod.RowNum = ChoonBoy.RowNum SET
ChoonBoy.[Scanned Data] = Null;

ChoonBoy_3
DELETE ChoonBoy.[Scanned Data]
FROM ChoonBoy
WHERE (((ChoonBoy.[Scanned Data]) Is Null));

ChoonBoy_4
INSERT INTO Inv_Prod ( RowNum, Invoice, Product )
SELECT [ChoonBoy].[RowNum]+1 AS Expr1, ChoonBoy.[Scanned Data],
ChoonBoy_1.[Scanned Data]
FROM ChoonBoy, ChoonBoy AS ChoonBoy_1
WHERE (((ChoonBoy.[Scanned Data]) Like "INV*") AND ((ChoonBoy_1.[Scanned
Data]) Like "AG*" Or (ChoonBoy_1.[Scanned Data]) Like "EG*") AND
((ChoonBoy_1.RowNum)>[ChoonBoy].[RowNum]));

--
Build a little, test a little.


ChoonBoy said:
Hi Karl,

Thank you for the effort and help. Some how, the result is not what I
expected. I must have missed a step or two.

Your assumption is correct, My data looks like this

rownum scanned data
1 INV12345
2 AG67891
3 AG67895
4 EG12344
5 EG12345
6 INV45666
7 AG67891
8 AG67895
9 AG97895
10 EG12344
11 EG12345

Note that Row 1 and Row 6 are Invoice Nos. Row 2 -5 and 7 - 8 are products
listed for the respective invoices

I want to generate this from the above data (either query or table)

Rownum Product Invoice
2 AG67891 INV12345
3 AG67895 INV12345
4 EG12344 INV12345
5 EG12345 INV12345
7 AG67891 INV45666
8 AG67895 INV45666
9 AG97895 INV45666
10 EG12344 INV45666
11 EG12345 INV45666

Thanks again.



KARL DEWEY said:
I assume your data looks like this --
RowNum Scanned Data
1 INV123
AG67891
AG67895
EG12344
EG12345
2 INV456
AG67891
AG67895
AG97895
EG12344
EG12345
having a Chr(13) & Chr(10) between invoice and products.
Create a table like this --
ROWNUM INVOICE PRODUCT
Long integer Text Text

Build these three queries --

ChoonBoy_1
INSERT INTO INV_PROD ( ROWNUM, INVOICE, PRODUCT )
SELECT Tbl_Input.RowNum, Left([Scanned Data],6) AS Expr1, Mid([Scanned
Data],InStr([Scanned Data],"EG"),7) AS Expr2
FROM Tbl_Input;

ChoonBoy_2
INSERT INTO INV_PROD ( ROWNUM, INVOICE, PRODUCT )
SELECT Tbl_Input.RowNum, Left([Scanned Data],6) AS Expr1, Mid([Scanned
Data],InStr([Scanned Data],"AG"),7) AS Expr2
FROM Tbl_Input;

ChoonBoy_3
UPDATE INV_PROD, Tbl_Input SET Tbl_Input.[Scanned Data] =
Replace(Replace([Scanned Data],[PRODUCT] & Chr(13) & Chr(10),""),[PRODUCT] &
Chr(13) & Chr(10),"")
WHERE (((Left([Scanned Data],6))=[INVOICE]));

Create a macro and Set Warning - No. Add these thre queries using action
OpenQuery as many times as you will have products on an invoice. And finish
macro with Set Warning - Yes.

The first two queries will append invoice and product to the Inv_Prod table,
one product at a time. Third query will remove the product and the carriage
return & line feed. When it is done there will be one product left with the
incoice number as the record will not have a carriage return & line feed on
the end of the data.

--
Build a little, test a little.


ChoonBoy said:
I do not have multiple invoices in the same record but I have an Invoice with
multiple products.

- First we scan the Invoice barcode which is captured in the text field.
- After this we scan the invoiced products barcode
- When the first Invoice + products are scanned, we continue with the other
invoices and products.

All the data are captured in one column, starting with Invoice code and
followed by product code in the next row.

1) My Table (Tbl_Input) only have 2 fields "RowNum" (Auto number) and
"Scanned Data" (text)

2) I use a datasheet Form (disabled sorting) with datasource pointing to the
Tbl_Input.

3) Any row of codes below the Invoice code are products associated to the
invoice until the next invoice number is encountered.

4) I need to build a report which must show "Invoice Code" as group header
and its products in the detail section.

5) I am not able to build the report with the only one column of mix codes.
I must somehow create another column so that it will show product's code in
one column and Invoice code in the next.

6) I do not know how to do No5 and will appreciate any help here.

Thank in advance.

:

Do you have multiple invoices in the same record?
If that is true then you need a two step process. First to put each invoice
in separate records with the product information.

Post your table and field names and sample data - maybe two records.

--
Build a little, test a little.


:

Actually it is hard to put in words but is something like this.

I am using a simple table with an auto number field (incremental) and a text
field.

1) A scanner is used to scan invoice nos to the text field (all invoices
starts with INVxxx
2) After the invoice nos is scanned, it is followed by scanning the products
into the same field but the next row (all products codes starts with either
EGxxxxx or AGxxxxx)
3) So you will have something like this for the column InvNos
/Prd1/Prd2/Prd3 etc. Next InvNos /Prd1/Prd2/Prd3/Prd4 etc, and the list goes
on.
4) I want to create a report to show each InvNos (group header) with Prds in
Detail section)
5) I am not able to do the report if I cannot associate the respective
InvNos to the Prds on the next column.
6) Using expression in the query (Scanned Data: IIf([barcode] Like
"inv*",[barcode],""), I am only able to move the InvNos to the next column.
7) The cell next to the prds is blank (which should be the respective InvNos.)

I hope I manage to give you a better picture.

Thanks



:

Are you sure you satated the row numbers correctly?
You have 13 rows for INVnos1 and 16 for INVnos2.

--
Build a little, test a little.


:

I am wondering if this is possible and appreciate any guide here.

I have a column (Scanned data) with incomplete values. Eg Row1 value =
"INVnos1" and Row2 until Row13 = Null. Row14 value = "INVnos2" and Row15
until Row30 = Null and so on.

How do I use codes / macro to fill Row2 until Row13 with value "INVnos1" and
Row15 until Row30 with value "INVnos2".

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
I am now able to get it working. The problem I am facing now is the transfer
is not complete if a record is deleted (with its autonumber deleted).
Anything after the deleted autonumber does not populate the column.

Is there a way to let the queries ignore the missing autonumbers and proceed
to populate the field as normal.

Thanks

KARL DEWEY said:
Same table as posted earlier but make a primary key of the two fields -
INVOICE, and PRODUCT.

Use the below four queries once, each following the other.
ChoonBoy_1
INSERT INTO Inv_Prod ( RowNum, Invoice, Product )
SELECT [ChoonBoy].[RowNum]+1 AS Expr1, ChoonBoy.[Scanned Data],
ChoonBoy_1.[Scanned Data]
FROM ChoonBoy, ChoonBoy AS ChoonBoy_1
WHERE (((ChoonBoy.[Scanned Data]) Like "INV*") AND
((ChoonBoy_1.RowNum)=[ChoonBoy].[RowNum]+1));

ChoonBoy_2
UPDATE Inv_Prod INNER JOIN ChoonBoy ON Inv_Prod.RowNum = ChoonBoy.RowNum SET
ChoonBoy.[Scanned Data] = Null;

ChoonBoy_3
DELETE ChoonBoy.[Scanned Data]
FROM ChoonBoy
WHERE (((ChoonBoy.[Scanned Data]) Is Null));

ChoonBoy_4
INSERT INTO Inv_Prod ( RowNum, Invoice, Product )
SELECT [ChoonBoy].[RowNum]+1 AS Expr1, ChoonBoy.[Scanned Data],
ChoonBoy_1.[Scanned Data]
FROM ChoonBoy, ChoonBoy AS ChoonBoy_1
WHERE (((ChoonBoy.[Scanned Data]) Like "INV*") AND ((ChoonBoy_1.[Scanned
Data]) Like "AG*" Or (ChoonBoy_1.[Scanned Data]) Like "EG*") AND
((ChoonBoy_1.RowNum)>[ChoonBoy].[RowNum]));

--
Build a little, test a little.


ChoonBoy said:
Hi Karl,

Thank you for the effort and help. Some how, the result is not what I
expected. I must have missed a step or two.

Your assumption is correct, My data looks like this

rownum scanned data
1 INV12345
2 AG67891
3 AG67895
4 EG12344
5 EG12345
6 INV45666
7 AG67891
8 AG67895
9 AG97895
10 EG12344
11 EG12345

Note that Row 1 and Row 6 are Invoice Nos. Row 2 -5 and 7 - 8 are products
listed for the respective invoices

I want to generate this from the above data (either query or table)

Rownum Product Invoice
2 AG67891 INV12345
3 AG67895 INV12345
4 EG12344 INV12345
5 EG12345 INV12345
7 AG67891 INV45666
8 AG67895 INV45666
9 AG97895 INV45666
10 EG12344 INV45666
11 EG12345 INV45666

Thanks again.



KARL DEWEY said:
I assume your data looks like this --
RowNum Scanned Data
1 INV123
AG67891
AG67895
EG12344
EG12345
2 INV456
AG67891
AG67895
AG97895
EG12344
EG12345
having a Chr(13) & Chr(10) between invoice and products.
Create a table like this --
ROWNUM INVOICE PRODUCT
Long integer Text Text

Build these three queries --

ChoonBoy_1
INSERT INTO INV_PROD ( ROWNUM, INVOICE, PRODUCT )
SELECT Tbl_Input.RowNum, Left([Scanned Data],6) AS Expr1, Mid([Scanned
Data],InStr([Scanned Data],"EG"),7) AS Expr2
FROM Tbl_Input;

ChoonBoy_2
INSERT INTO INV_PROD ( ROWNUM, INVOICE, PRODUCT )
SELECT Tbl_Input.RowNum, Left([Scanned Data],6) AS Expr1, Mid([Scanned
Data],InStr([Scanned Data],"AG"),7) AS Expr2
FROM Tbl_Input;

ChoonBoy_3
UPDATE INV_PROD, Tbl_Input SET Tbl_Input.[Scanned Data] =
Replace(Replace([Scanned Data],[PRODUCT] & Chr(13) & Chr(10),""),[PRODUCT] &
Chr(13) & Chr(10),"")
WHERE (((Left([Scanned Data],6))=[INVOICE]));

Create a macro and Set Warning - No. Add these thre queries using action
OpenQuery as many times as you will have products on an invoice. And finish
macro with Set Warning - Yes.

The first two queries will append invoice and product to the Inv_Prod table,
one product at a time. Third query will remove the product and the carriage
return & line feed. When it is done there will be one product left with the
incoice number as the record will not have a carriage return & line feed on
the end of the data.

--
Build a little, test a little.


:

I do not have multiple invoices in the same record but I have an Invoice with
multiple products.

- First we scan the Invoice barcode which is captured in the text field.
- After this we scan the invoiced products barcode
- When the first Invoice + products are scanned, we continue with the other
invoices and products.

All the data are captured in one column, starting with Invoice code and
followed by product code in the next row.

1) My Table (Tbl_Input) only have 2 fields "RowNum" (Auto number) and
"Scanned Data" (text)

2) I use a datasheet Form (disabled sorting) with datasource pointing to the
Tbl_Input.

3) Any row of codes below the Invoice code are products associated to the
invoice until the next invoice number is encountered.

4) I need to build a report which must show "Invoice Code" as group header
and its products in the detail section.

5) I am not able to build the report with the only one column of mix codes.
I must somehow create another column so that it will show product's code in
one column and Invoice code in the next.

6) I do not know how to do No5 and will appreciate any help here.

Thank in advance.

:

Do you have multiple invoices in the same record?
If that is true then you need a two step process. First to put each invoice
in separate records with the product information.

Post your table and field names and sample data - maybe two records.

--
Build a little, test a little.


:

Actually it is hard to put in words but is something like this.

I am using a simple table with an auto number field (incremental) and a text
field.

1) A scanner is used to scan invoice nos to the text field (all invoices
starts with INVxxx
2) After the invoice nos is scanned, it is followed by scanning the products
into the same field but the next row (all products codes starts with either
EGxxxxx or AGxxxxx)
3) So you will have something like this for the column InvNos
/Prd1/Prd2/Prd3 etc. Next InvNos /Prd1/Prd2/Prd3/Prd4 etc, and the list goes
on.
4) I want to create a report to show each InvNos (group header) with Prds in
Detail section)
5) I am not able to do the report if I cannot associate the respective
InvNos to the Prds on the next column.
6) Using expression in the query (Scanned Data: IIf([barcode] Like
"inv*",[barcode],""), I am only able to move the InvNos to the next column.
7) The cell next to the prds is blank (which should be the respective InvNos.)

I hope I manage to give you a better picture.

Thanks



:

Are you sure you satated the row numbers correctly?
You have 13 rows for INVnos1 and 16 for INVnos2.

--
Build a little, test a little.


:

I am wondering if this is possible and appreciate any guide here.

I have a column (Scanned data) with incomplete values. Eg Row1 value =
"INVnos1" and Row2 until Row13 = Null. Row14 value = "INVnos2" and Row15
until Row30 = Null and so on.

How do I use codes / macro to fill Row2 until Row13 with value "INVnos1" and
Row15 until Row30 with value "INVnos2".

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top