thank you!! I will test it this weekend and let you know!
-gary
:
Gary,
Okay, I get it now. That is a problem. Thinking how I would solve
the
problem. Here's a thought. Have an extra field and write a routine
that
says
If SOHDR update MyNewField to 1 and all subsequent till you see SOHDR
again
and then change to MyNewField to 2 and so on. Now I'm never written a
loop
like that but it sounds possible. Here's some untested code I found in
the
newsgroups awhile ago and then I wasn't so diligent about getting the
name
of the individual (So THANKS to the person!!!):
Copy the below into a module and replace the field and table names
where
indicated and make a back-up (most important part) and test it.
***START OF CODE
Function CopyFieldRecords(pstrRST As String, pstrField As String,
pstrID
As
String) As Boolean
Dim db As Database
Dim rec As Recordset
Dim vCopyDown As Variant
CopyFieldRecords = True
On Error GoTo err_copyrecords
vCopyDown = Null
Set db = CurrentDb()
Set rec = db.OpenRecordset("Select * FROM [" & YourTable & "]")
While Not rec.EOF
'If the field isn't blank then use this to copy down
If Nz(rec(pstrField), "") <> "" Then
vCopyDown = rec(pstrField)
Else
'Only if we have something to copy down
If Nz(vCopyDown, "") <> "" Then
rec.Edit
rec(pstrField) = vCopyDown
rec.Update
End If
End If
rec.MoveNext
Wend
exit_copyrecords:
Exit Function
err_copyrecords:
MsgBox Error, vbCritical, "Copy Fields Down Records"
CopyFieldRecords = False
GoTo exit_copyrecords
End Function
***END OF CODE
'Call the function like this
pstrRST = "YourTableName"
pstrFieldToCopy = "YourFieldName" 'name of the field that you want
to
copy down
pstrID = "IDField"
Call CopyFieldRecords(pstrRST, pstrFieldToCopy, pstrID)
--
Gina Whipp
"I feel I have been denied critical, need to know, information!" -
Tremors
II
message
Hi again,
There is no link, this is the problem I am having, I am just
horrible
at
trying to explain it! What I am hoping to do is somehow take a
field
from
the /SOHDR record and place it into the specific /SOSUM record for
the
order,
then I can run more queries to update the ship to in the /SOSUM
record.
Does that make sense? I am confusing myself...
:
"/SOHDR", "blah", "blah", blah"
"/SOLI","blah", "blah"
"/SOSUM", "Order1", "blah", blah", "blah", "blah", blah"
"/SOHDR", "blah", "blah", blah", "blah", "blah"
"/SOLI","blah", "blah", "blah", "blah"
"/SOLI","blah", "blah", "blah", "blah"
"/SOSUM", "Order2", "blah", blah", "blah", "blah"
See above, what links the EDI file together. In my file there is
something
on every line that lets me know which ship to goes to what order.
Is
this
the case with yours?
for instance
Order1, blah, blah
Order1, ShipTo, blah
Order2, blah, blah
Order2, ShipTo, blah
--
Gina Whipp
"I feel I have been denied critical, need to know, information!" -
Tremors
II
message
Hi Gina,
Thank you so much for the reply! I do have a temp table that I
push
everything into, I was just wanting to automate an update query
to
add
the
DC
# needed for shipping purposes. Suprisingly, the data is coming
in
fairly
clean, I just need to be able to update the shipping line on the
/SOSUM
line
- but before I can do that, I need to know how to correctly add
data
to
a
specific record.
For example, my data comes like this (this is 2 orders):
"/SOHDR", "blah", "blah", blah"
"/SOLI","blah", "blah"
"/SOSUM", "blah", "blah", blah", "blah", "blah", blah"
"/SOHDR", "blah", "blah", blah", "blah", "blah"
"/SOLI","blah", "blah", "blah", "blah"
"/SOLI","blah", "blah", "blah", "blah"
"/SOSUM", "blah", "blah", blah", "blah", "blah"
Both of the above orders are coming from one customer, but they
want
them
shipped to separate locations (known by a field in the "/SOHDR"
record). I
am
hoping to be able to create a unique ID that will only fit into
one
order
so
I can then tell it to update the correct "/SOSUM" record with the
correct
ship address.
Please help, thanks!
-gary
:
Gary,
I get an 856, 861 and 870 EDI file. The best way I found to
handle
this
is
import them into a temp table, which generates an error report
if
anything
comes in hinky and the update/append to the real tables once the
data
is
validated. That way I have 3 steps.
1. Click button, import file
2. Validate file (temp table & error report)
3. Append/Update 'real' tables with the click of a button
--
Gina Whipp
"I feel I have been denied critical, need to know,
information!" -
Tremors
II
in
message
Hi all,
I am receiving a .csv file of EDI data and am needing to
convert
it
slightly
for import into our shipping database - unfortunately, I do
not
have
any
say
in how the information will be presented to me and it comes in
an
interesting
format. The table comes with fields named [field1] though
[fieldX]
For each order that is sent in, there will be at least 3
entries.
Currently, in [field1], there will be three possible entries,
always
in
the
following order:
/SOHDR - this is the header information line
/SOLI - line item, this can sometimes be more than one in any
order
/SOSUM - summary line
if there are multiple orders, then this same sequence will
repeat
in
the
rows below. As there are currently no relating fields in any
of
the
order
records, I am wanting to create a unique identifier for each
set
(order)
that
I am pulling from the /SOHDR line and then insert it into the
/SOLI
and
/SOSUM lines (this way I can bring in separate shipping
addresses).
How
would I insert it (using an update query) into the field [XX]
but
not
have
it
update all of the records, only the one order?
Sorry, I am not sure if this is making any sense, but any help
would
be
appreciated!! Thanks!
-gary
