Adding records to subform

Z

Zedbiker

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
 
Z

Zedbiker

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 said:
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 said:
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
Click to expand...
Click to expand...
 
Z

Zedbiker

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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
A

Alex Dybenko

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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Z

Zedbiker

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 said:
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 said:
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 said:
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


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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

multi criteria code 2
Duplicate Records That Contain Composite PK 4
Message Box to Display a count of Records 3
Copy old subform records to new Form's subform 1
Go to a record in a subform 0
Filter records in subform with VB code. 4
Using an Access form to Mass Update a table 7
Using a Form to Mass Update a Table 1

Top