Hi
My parent form (backpackorder)takes information from a table which lists the
parts used in an asembly. Each part is linked to a subform by its part id
(backpackpartid). The subform contains the order information. When I press a
button on the main form I want to automatically order all the parts. I have
tried to do this using the code below (probably not best way but it sort of
works. Never professed to being a programmer :-) ). It runs through the first
part ok adding the record automatically, then goes to the next part and stops
at the first record.
What am I doing wrong? Msgbox's have been added for error trapping. Many
thanks for any help.

Private Sub CmdOrder_Click()
On Error GoTo Err_CmdOrder_Click

Dim BatchNo As String
Dim ComponentName As String
Dim Priority As String
Dim Signature As String
Dim Quantity As Integer
Dim i As Integer
Dim IDno, IDinc As Integer

DoCmd.GoToRecord , , acLast
IDno = Me![BackPackPartID].Value
MsgBox (IDno)
DoCmd.GoToRecord , , acFirst
IDinc = 1
If IDinc <= IDno Then
IDinc = Me![BackPackPartID].Value
MsgBox (IDinc)
Me![BackPackSubform].SetFocus
DoCmd.GoToRecord , , acLast
BatchNo = Me![BackPackSubform]![Batch]
ComponentName = Me![Component Name]
Quantity = Me![BackPackSubform]![Quantity]

If Me![BackPackPartID] = "1" Then
Signature = Me![CmbSignature].Column(1)
Priority = Me![CmbPriorityLevel].Column(0)
End If

i = CInt(BatchNo)
MsgBox (i)
i = i + 1
BatchNo = CStr(i)
MsgBox (BatchNo)
BatchNo = Format([BatchNo], "000")
MsgBox (BatchNo)

DoCmd.GoToRecord , , acNewRec
Me![BackPackSubform]![Batch] = BatchNo
Me![BackPackSubform]![Component Name] = ComponentName
Me![BackPackSubform]![Signature] = Signature
Me![BackPackSubform]![Priority] = Priority
Me![BackPackSubform]![Quantity] = Quantity
IDinc = IDinc + 1
MsgBox (IDinc)
DoCmd.GoToRecord acDataForm, "BackPackOrder", acGoTo, IDinc
MsgBox ("Continue?")

End If

Exit_CmdOrder_Click:
Exit Sub
Err_CmdOrder_Click:
MsgBox Err.Description
Resume Exit_CmdOrder_Click

End Sub

Hi,
try to write an append (insert) query instead, as I see - it easy solve you
problem. And requery subform after you run it

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com


"Zedbiker" <(E-Mail Removed)> wrote in message
news160B96D-4D90-4136-A508-(E-Mail Removed)...
> Hi
> My parent form (backpackorder)takes information from a table which lists
> the
> parts used in an asembly. Each part is linked to a subform by its part id
> (backpackpartid). The subform contains the order information. When I press
> a
> button on the main form I want to automatically order all the parts. I
> have
> tried to do this using the code below (probably not best way but it sort
> of
> works. Never professed to being a programmer :-) ). It runs through the
> first
> part ok adding the record automatically, then goes to the next part and
> stops
> at the first record.
> What am I doing wrong? Msgbox's have been added for error trapping. Many
> thanks for any help.
>
> Private Sub CmdOrder_Click()
> On Error GoTo Err_CmdOrder_Click
>
> Dim BatchNo As String
> Dim ComponentName As String
> Dim Priority As String
> Dim Signature As String
> Dim Quantity As Integer
> Dim i As Integer
> Dim IDno, IDinc As Integer
>
> DoCmd.GoToRecord , , acLast
> IDno = Me![BackPackPartID].Value
> MsgBox (IDno)
> DoCmd.GoToRecord , , acFirst
> IDinc = 1
> If IDinc <= IDno Then
> IDinc = Me![BackPackPartID].Value
> MsgBox (IDinc)
> Me![BackPackSubform].SetFocus
> DoCmd.GoToRecord , , acLast
> BatchNo = Me![BackPackSubform]![Batch]
> ComponentName = Me![Component Name]
> Quantity = Me![BackPackSubform]![Quantity]
>
> If Me![BackPackPartID] = "1" Then
> Signature = Me![CmbSignature].Column(1)
> Priority = Me![CmbPriorityLevel].Column(0)
> End If
>
> i = CInt(BatchNo)
> MsgBox (i)
> i = i + 1
> BatchNo = CStr(i)
> MsgBox (BatchNo)
> BatchNo = Format([BatchNo], "000")
> MsgBox (BatchNo)
>
> DoCmd.GoToRecord , , acNewRec
> Me![BackPackSubform]![Batch] = BatchNo
> Me![BackPackSubform]![Component Name] = ComponentName
> Me![BackPackSubform]![Signature] = Signature
> Me![BackPackSubform]![Priority] = Priority
> Me![BackPackSubform]![Quantity] = Quantity
> IDinc = IDinc + 1
> MsgBox (IDinc)
> DoCmd.GoToRecord acDataForm, "BackPackOrder", acGoTo, IDinc
> MsgBox ("Continue?")
>
> End If
>
> Exit_CmdOrder_Click:
> Exit Sub
> Err_CmdOrder_Click:
> MsgBox Err.Description
> Resume Exit_CmdOrder_Click
>
> End Sub
>

Hi Alex
Thank you for your reply. Excuse my ignorance. Do you mean that I should add
this into my VBA. I have not used an append query before so this again is new
ground for me. Can you suggest a tutorial website that can give me more
advice to save your time.
This database started as a basic ordering database and as it has progressed
colleagues have asked for more and more additions which has meant a steep
learning curve for me. I hope this explains my ignorance.
Thank you again for any help you can offer.
Best Regards
Ian
P.S. This is my 4th or 5th attempt at replying to you. This website keeps
crashing and I lose my reply. Very frustrating!!


"Alex Dybenko" wrote:

> Hi,
> try to write an append (insert) query instead, as I see - it easy solve you
> problem. And requery subform after you run it
>
> --
> Best regards,
> ___________
> Alex Dybenko (MVP)
> http://accessblog.net
> http://www.PointLtd.com
>
>
> "Zedbiker" <(E-Mail Removed)> wrote in message
> news160B96D-4D90-4136-A508-(E-Mail Removed)...
> > Hi
> > My parent form (backpackorder)takes information from a table which lists
> > the
> > parts used in an asembly. Each part is linked to a subform by its part id
> > (backpackpartid). The subform contains the order information. When I press
> > a
> > button on the main form I want to automatically order all the parts. I
> > have
> > tried to do this using the code below (probably not best way but it sort
> > of
> > works. Never professed to being a programmer :-) ). It runs through the
> > first
> > part ok adding the record automatically, then goes to the next part and
> > stops
> > at the first record.
> > What am I doing wrong? Msgbox's have been added for error trapping. Many
> > thanks for any help.
> >
> > Private Sub CmdOrder_Click()
> > On Error GoTo Err_CmdOrder_Click
> >
> > Dim BatchNo As String
> > Dim ComponentName As String
> > Dim Priority As String
> > Dim Signature As String
> > Dim Quantity As Integer
> > Dim i As Integer
> > Dim IDno, IDinc As Integer
> >
> > DoCmd.GoToRecord , , acLast
> > IDno = Me![BackPackPartID].Value
> > MsgBox (IDno)
> > DoCmd.GoToRecord , , acFirst
> > IDinc = 1
> > If IDinc <= IDno Then
> > IDinc = Me![BackPackPartID].Value
> > MsgBox (IDinc)
> > Me![BackPackSubform].SetFocus
> > DoCmd.GoToRecord , , acLast
> > BatchNo = Me![BackPackSubform]![Batch]
> > ComponentName = Me![Component Name]
> > Quantity = Me![BackPackSubform]![Quantity]
> >
> > If Me![BackPackPartID] = "1" Then
> > Signature = Me![CmbSignature].Column(1)
> > Priority = Me![CmbPriorityLevel].Column(0)
> > End If
> >
> > i = CInt(BatchNo)
> > MsgBox (i)
> > i = i + 1
> > BatchNo = CStr(i)
> > MsgBox (BatchNo)
> > BatchNo = Format([BatchNo], "000")
> > MsgBox (BatchNo)
> >
> > DoCmd.GoToRecord , , acNewRec
> > Me![BackPackSubform]![Batch] = BatchNo
> > Me![BackPackSubform]![Component Name] = ComponentName
> > Me![BackPackSubform]![Signature] = Signature
> > Me![BackPackSubform]![Priority] = Priority
> > Me![BackPackSubform]![Quantity] = Quantity
> > IDinc = IDinc + 1
> > MsgBox (IDinc)
> > DoCmd.GoToRecord acDataForm, "BackPackOrder", acGoTo, IDinc
> > MsgBox ("Continue?")
> >
> > End If
> >
> > Exit_CmdOrder_Click:
> > Exit Sub
> > Err_CmdOrder_Click:
> > MsgBox Err.Description
> > Resume Exit_CmdOrder_Click
> >
> > End Sub
> >
>

Hi again
I changed my If statement to a 'Do While...'. This allowed my program to
work although I am sure the way you suggested would be better and less
clumsy.
Thank you
Ian

"Zedbiker" wrote:

> Hi Alex
> Thank you for your reply. Excuse my ignorance. Do you mean that I should add
> this into my VBA. I have not used an append query before so this again is new
> ground for me. Can you suggest a tutorial website that can give me more
> advice to save your time.
> This database started as a basic ordering database and as it has progressed
> colleagues have asked for more and more additions which has meant a steep
> learning curve for me. I hope this explains my ignorance.
> Thank you again for any help you can offer.
> Best Regards
> Ian
> P.S. This is my 4th or 5th attempt at replying to you. This website keeps
> crashing and I lose my reply. Very frustrating!!
>
>
> "Alex Dybenko" wrote:
>
> > Hi,
> > try to write an append (insert) query instead, as I see - it easy solve you
> > problem. And requery subform after you run it
> >
> > --
> > Best regards,
> > ___________
> > Alex Dybenko (MVP)
> > http://accessblog.net
> > http://www.PointLtd.com
> >
> >
> > "Zedbiker" <(E-Mail Removed)> wrote in message
> > news160B96D-4D90-4136-A508-(E-Mail Removed)...
> > > Hi
> > > My parent form (backpackorder)takes information from a table which lists
> > > the
> > > parts used in an asembly. Each part is linked to a subform by its part id
> > > (backpackpartid). The subform contains the order information. When I press
> > > a
> > > button on the main form I want to automatically order all the parts. I
> > > have
> > > tried to do this using the code below (probably not best way but it sort
> > > of
> > > works. Never professed to being a programmer :-) ). It runs through the
> > > first
> > > part ok adding the record automatically, then goes to the next part and
> > > stops
> > > at the first record.
> > > What am I doing wrong? Msgbox's have been added for error trapping. Many
> > > thanks for any help.
> > >
> > > Private Sub CmdOrder_Click()
> > > On Error GoTo Err_CmdOrder_Click
> > >
> > > Dim BatchNo As String
> > > Dim ComponentName As String
> > > Dim Priority As String
> > > Dim Signature As String
> > > Dim Quantity As Integer
> > > Dim i As Integer
> > > Dim IDno, IDinc As Integer
> > >
> > > DoCmd.GoToRecord , , acLast
> > > IDno = Me![BackPackPartID].Value
> > > MsgBox (IDno)
> > > DoCmd.GoToRecord , , acFirst
> > > IDinc = 1
> > > If IDinc <= IDno Then
> > > IDinc = Me![BackPackPartID].Value
> > > MsgBox (IDinc)
> > > Me![BackPackSubform].SetFocus
> > > DoCmd.GoToRecord , , acLast
> > > BatchNo = Me![BackPackSubform]![Batch]
> > > ComponentName = Me![Component Name]
> > > Quantity = Me![BackPackSubform]![Quantity]
> > >
> > > If Me![BackPackPartID] = "1" Then
> > > Signature = Me![CmbSignature].Column(1)
> > > Priority = Me![CmbPriorityLevel].Column(0)
> > > End If
> > >
> > > i = CInt(BatchNo)
> > > MsgBox (i)
> > > i = i + 1
> > > BatchNo = CStr(i)
> > > MsgBox (BatchNo)
> > > BatchNo = Format([BatchNo], "000")
> > > MsgBox (BatchNo)
> > >
> > > DoCmd.GoToRecord , , acNewRec
> > > Me![BackPackSubform]![Batch] = BatchNo
> > > Me![BackPackSubform]![Component Name] = ComponentName
> > > Me![BackPackSubform]![Signature] = Signature
> > > Me![BackPackSubform]![Priority] = Priority
> > > Me![BackPackSubform]![Quantity] = Quantity
> > > IDinc = IDinc + 1
> > > MsgBox (IDinc)
> > > DoCmd.GoToRecord acDataForm, "BackPackOrder", acGoTo, IDinc
> > > MsgBox ("Continue?")
> > >
> > > End If
> > >
> > > Exit_CmdOrder_Click:
> > > Exit Sub
> > > Err_CmdOrder_Click:
> > > MsgBox Err.Description
> > > Resume Exit_CmdOrder_Click
> > >
> > > End Sub
> > >
> >

Hi,
I think best would be to read some book on Access, if you have one, else -
try to google for building access queries
If you get the idea how it works - you can then easy compete such tasks,
queries - is most powerful feature of Access


--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

"Zedbiker" <(E-Mail Removed)> wrote in message
news:E9FE9A53-79D3-40AE-889F-(E-Mail Removed)...
> Hi Alex
> Thank you for your reply. Excuse my ignorance. Do you mean that I should
> add
> this into my VBA. I have not used an append query before so this again is
> new
> ground for me. Can you suggest a tutorial website that can give me more
> advice to save your time.
> This database started as a basic ordering database and as it has
> progressed
> colleagues have asked for more and more additions which has meant a steep
> learning curve for me. I hope this explains my ignorance.
> Thank you again for any help you can offer.
> Best Regards
> Ian
> P.S. This is my 4th or 5th attempt at replying to you. This website keeps
> crashing and I lose my reply. Very frustrating!!
>
>
> "Alex Dybenko" wrote:
>
>> Hi,
>> try to write an append (insert) query instead, as I see - it easy solve
>> you
>> problem. And requery subform after you run it
>>
>> --
>> Best regards,
>> ___________
>> Alex Dybenko (MVP)
>> http://accessblog.net
>> http://www.PointLtd.com
>>
>>
>> "Zedbiker" <(E-Mail Removed)> wrote in message
>> news160B96D-4D90-4136-A508-(E-Mail Removed)...
>> > Hi
>> > My parent form (backpackorder)takes information from a table which
>> > lists
>> > the
>> > parts used in an asembly. Each part is linked to a subform by its part
>> > id
>> > (backpackpartid). The subform contains the order information. When I
>> > press
>> > a
>> > button on the main form I want to automatically order all the parts. I
>> > have
>> > tried to do this using the code below (probably not best way but it
>> > sort
>> > of
>> > works. Never professed to being a programmer :-) ). It runs through the
>> > first
>> > part ok adding the record automatically, then goes to the next part and
>> > stops
>> > at the first record.
>> > What am I doing wrong? Msgbox's have been added for error trapping.
>> > Many
>> > thanks for any help.
>> >
>> > Private Sub CmdOrder_Click()
>> > On Error GoTo Err_CmdOrder_Click
>> >
>> > Dim BatchNo As String
>> > Dim ComponentName As String
>> > Dim Priority As String
>> > Dim Signature As String
>> > Dim Quantity As Integer
>> > Dim i As Integer
>> > Dim IDno, IDinc As Integer
>> >
>> > DoCmd.GoToRecord , , acLast
>> > IDno = Me![BackPackPartID].Value
>> > MsgBox (IDno)
>> > DoCmd.GoToRecord , , acFirst
>> > IDinc = 1
>> > If IDinc <= IDno Then
>> > IDinc = Me![BackPackPartID].Value
>> > MsgBox (IDinc)
>> > Me![BackPackSubform].SetFocus
>> > DoCmd.GoToRecord , , acLast
>> > BatchNo = Me![BackPackSubform]![Batch]
>> > ComponentName = Me![Component Name]
>> > Quantity = Me![BackPackSubform]![Quantity]
>> >
>> > If Me![BackPackPartID] = "1" Then
>> > Signature = Me![CmbSignature].Column(1)
>> > Priority = Me![CmbPriorityLevel].Column(0)
>> > End If
>> >
>> > i = CInt(BatchNo)
>> > MsgBox (i)
>> > i = i + 1
>> > BatchNo = CStr(i)
>> > MsgBox (BatchNo)
>> > BatchNo = Format([BatchNo], "000")
>> > MsgBox (BatchNo)
>> >
>> > DoCmd.GoToRecord , , acNewRec
>> > Me![BackPackSubform]![Batch] = BatchNo
>> > Me![BackPackSubform]![Component Name] = ComponentName
>> > Me![BackPackSubform]![Signature] = Signature
>> > Me![BackPackSubform]![Priority] = Priority
>> > Me![BackPackSubform]![Quantity] = Quantity
>> > IDinc = IDinc + 1
>> > MsgBox (IDinc)
>> > DoCmd.GoToRecord acDataForm, "BackPackOrder", acGoTo, IDinc
>> > MsgBox ("Continue?")
>> >
>> > End If
>> >
>> > Exit_CmdOrder_Click:
>> > Exit Sub
>> > Err_CmdOrder_Click:
>> > MsgBox Err.Description
>> > Resume Exit_CmdOrder_Click
>> >
>> > End Sub
>> >
>>

Thank you for your patience. I will certainly do some reading about queries.
Up to now I think I have just touched on the surface of what they can do.
Thank you again for your advice.
Ian

"Alex Dybenko" wrote:

> Hi,
> I think best would be to read some book on Access, if you have one, else -
> try to google for building access queries
> If you get the idea how it works - you can then easy compete such tasks,
> queries - is most powerful feature of Access
>
>
> --
> Best regards,
> ___________
> Alex Dybenko (MVP)
> http://accessblog.net
> http://www.PointLtd.com
>
> "Zedbiker" <(E-Mail Removed)> wrote in message
> news:E9FE9A53-79D3-40AE-889F-(E-Mail Removed)...
> > Hi Alex
> > Thank you for your reply. Excuse my ignorance. Do you mean that I should
> > add
> > this into my VBA. I have not used an append query before so this again is
> > new
> > ground for me. Can you suggest a tutorial website that can give me more
> > advice to save your time.
> > This database started as a basic ordering database and as it has
> > progressed
> > colleagues have asked for more and more additions which has meant a steep
> > learning curve for me. I hope this explains my ignorance.
> > Thank you again for any help you can offer.
> > Best Regards
> > Ian
> > P.S. This is my 4th or 5th attempt at replying to you. This website keeps
> > crashing and I lose my reply. Very frustrating!!
> >
> >
> > "Alex Dybenko" wrote:
> >
> >> Hi,
> >> try to write an append (insert) query instead, as I see - it easy solve
> >> you
> >> problem. And requery subform after you run it
> >>
> >> --
> >> Best regards,
> >> ___________
> >> Alex Dybenko (MVP)
> >> http://accessblog.net
> >> http://www.PointLtd.com
> >>
> >>
> >> "Zedbiker" <(E-Mail Removed)> wrote in message
> >> news160B96D-4D90-4136-A508-(E-Mail Removed)...
> >> > Hi
> >> > My parent form (backpackorder)takes information from a table which
> >> > lists
> >> > the
> >> > parts used in an asembly. Each part is linked to a subform by its part
> >> > id
> >> > (backpackpartid). The subform contains the order information. When I
> >> > press
> >> > a
> >> > button on the main form I want to automatically order all the parts. I
> >> > have
> >> > tried to do this using the code below (probably not best way but it
> >> > sort
> >> > of
> >> > works. Never professed to being a programmer :-) ). It runs through the
> >> > first
> >> > part ok adding the record automatically, then goes to the next part and
> >> > stops
> >> > at the first record.
> >> > What am I doing wrong? Msgbox's have been added for error trapping.
> >> > Many
> >> > thanks for any help.
> >> >
> >> > Private Sub CmdOrder_Click()
> >> > On Error GoTo Err_CmdOrder_Click
> >> >
> >> > Dim BatchNo As String
> >> > Dim ComponentName As String
> >> > Dim Priority As String
> >> > Dim Signature As String
> >> > Dim Quantity As Integer
> >> > Dim i As Integer
> >> > Dim IDno, IDinc As Integer
> >> >
> >> > DoCmd.GoToRecord , , acLast
> >> > IDno = Me![BackPackPartID].Value
> >> > MsgBox (IDno)
> >> > DoCmd.GoToRecord , , acFirst
> >> > IDinc = 1
> >> > If IDinc <= IDno Then
> >> > IDinc = Me![BackPackPartID].Value
> >> > MsgBox (IDinc)
> >> > Me![BackPackSubform].SetFocus
> >> > DoCmd.GoToRecord , , acLast
> >> > BatchNo = Me![BackPackSubform]![Batch]
> >> > ComponentName = Me![Component Name]
> >> > Quantity = Me![BackPackSubform]![Quantity]
> >> >
> >> > If Me![BackPackPartID] = "1" Then
> >> > Signature = Me![CmbSignature].Column(1)
> >> > Priority = Me![CmbPriorityLevel].Column(0)
> >> > End If
> >> >
> >> > i = CInt(BatchNo)
> >> > MsgBox (i)
> >> > i = i + 1
> >> > BatchNo = CStr(i)
> >> > MsgBox (BatchNo)
> >> > BatchNo = Format([BatchNo], "000")
> >> > MsgBox (BatchNo)
> >> >
> >> > DoCmd.GoToRecord , , acNewRec
> >> > Me![BackPackSubform]![Batch] = BatchNo
> >> > Me![BackPackSubform]![Component Name] = ComponentName
> >> > Me![BackPackSubform]![Signature] = Signature
> >> > Me![BackPackSubform]![Priority] = Priority
> >> > Me![BackPackSubform]![Quantity] = Quantity
> >> > IDinc = IDinc + 1
> >> > MsgBox (IDinc)
> >> > DoCmd.GoToRecord acDataForm, "BackPackOrder", acGoTo, IDinc
> >> > MsgBox ("Continue?")
> >> >
> >> > End If
> >> >
> >> > Exit_CmdOrder_Click:
> >> > Exit Sub
> >> > Err_CmdOrder_Click:
> >> > MsgBox Err.Description
> >> > Resume Exit_CmdOrder_Click
> >> >
> >> > End Sub
> >> >
> >>
>