Creating a New record

D

Deano

I have already posted this a couple of days ago and have come stuck with the
help I received. What I am trying to do is create a new record in a header
table and details in a detail table. To do this I am have 2 list boxes.
One (Header) with a list of suppliers and the 2nd(Footer) is a list of parts
that are related to the supplier selected. I then want to be able to click
a button which will create an order with the data in the listboxes. here's
my code below.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim lngIDNumber As Long
Set db = CurrentDb
Set rs = db.OpenRecordset("MasterTable")
Set rs1 = db.OpenRecordset("DetailsTable")
rs.AddNew
rs![SupplierID] = lstSuppliers.Column(0)
rs![Description] = "Testing Order Enquiry Entries"
rs![Status] = "Created"
rs![Notes] = "This is great"
lngIDNumber = rs![OrderEnquiryID]
rs.Update

For k = 0 To Me.lstPartsReq.ListCount
Debug.Assert Me.lstPartsReq.ItemData(k)
rs1.AddNew
rs1![OEID] = lngIDNumber
rs1![PartID] = lstPartsReq.Column(0)
rs1![Description] = lstPartsReq.Column(1)
rs1![Quantity] = lstPartsReq.Column(2)
rs1.Update
Next
rs.Close
rs1.Close

The problem is that no data appears in the fields within the detail records.
I have also learned that the Me.lstPartsReq.ListCount will include the
headers on the listbox and it only include 1 field of the listbox which is
ithe PartID. I need to populate a few more fields.

I know this post is a little long but I am stuck. If anyone can help or
suggest another way it would be much appreciated.

Thanks
 
N

Nikos Yannacopoulos

Deano,

The first problem here is your references to the listboxes. You are not
referencing them properly, so Access thinks they are local, implicitly
declared variables, which have not been assigned values, therefore they are
Null, and this is what they put in the table fields! If the code is in the
form's own module, then your references should simply be:
Me.lstSuppliers instead of lstSuppliers,
Me.lstSuppliers.Column(0) instead of lstSuppliers.Column(0),
Me.lstPartsReq.Column(0) instead of lstPartsReq.Column(0)
etc.
If, on the other hand, the code is in a general module, you need explicit
referencing, involving the form's name. Assuming the form is called MyForm,
the proper referencing would be:
Forms![MyForm]!lstPartsReq.Column(0)
etc.

Also: your code will add a record for each and every row in the listbox,
regardless if it's selected or not! If this was indeed intended, then your
code must be something like:

For k = 0 To Me.lstPartsReq.ListCount - 1
rs1.AddNew
rs1![OEID] = lngIDNumber
rs1![PartID] = Me.lstPartsReq.Column(0)
rs1![Description] = Me.lstPartsReq.Column(1)
rs1![Quantity] = Me.lstPartsReq.Column(2)
rs1.Update
Next

with the assumption that the code is in the form's own module. Note: the
Me.lstPartsReq.ListCount will NOT include the headers, that's whyI have
limited the upper limit by adding the -1 at the end of the For statement.

If, on the other hand, this is a multi-select listbox and you only need to
cretae records for the selected items, the code should become:

For each itm in Me.lstPartsReq.ItemsSelected
rs1.AddNew
rs1![OEID] = lngIDNumber
rs1![PartID] = Me.lstPartsReq.Column(0, itm)
rs1![Description] = Me.lstPartsReq.Column(1, itm)
rs1![Quantity] = Me.lstPartsReq.Column(2, itm)
rs1.Update
Next

HTH,
Nikos


Deano said:
I have already posted this a couple of days ago and have come stuck with the
help I received. What I am trying to do is create a new record in a header
table and details in a detail table. To do this I am have 2 list boxes.
One (Header) with a list of suppliers and the 2nd(Footer) is a list of parts
that are related to the supplier selected. I then want to be able to click
a button which will create an order with the data in the listboxes. here's
my code below.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim lngIDNumber As Long
Set db = CurrentDb
Set rs = db.OpenRecordset("MasterTable")
Set rs1 = db.OpenRecordset("DetailsTable")
rs.AddNew
rs![SupplierID] = lstSuppliers.Column(0)
rs![Description] = "Testing Order Enquiry Entries"
rs![Status] = "Created"
rs![Notes] = "This is great"
lngIDNumber = rs![OrderEnquiryID]
rs.Update

For k = 0 To Me.lstPartsReq.ListCount
Debug.Assert Me.lstPartsReq.ItemData(k)
rs1.AddNew
rs1![OEID] = lngIDNumber
rs1![PartID] = lstPartsReq.Column(0)
rs1![Description] = lstPartsReq.Column(1)
rs1![Quantity] = lstPartsReq.Column(2)
rs1.Update
Next
rs.Close
rs1.Close

The problem is that no data appears in the fields within the detail records.
I have also learned that the Me.lstPartsReq.ListCount will include the
headers on the listbox and it only include 1 field of the listbox which is
ithe PartID. I need to populate a few more fields.

I know this post is a little long but I am stuck. If anyone can help or
suggest another way it would be much appreciated.

Thanks
Click to expand...
 
D

Deano

Thanks for the comments and response which is much appreciated I have
understood what you have said and suggestions but I am unable to get it to
work. You took the assumsion correct in that the code is a general module
under the OnClick button. Unfortunately it still inserts 2 blank
records(Details) even though there is only 1 record in the listbox. Here's
my new code

Private Sub cmdGenEnquiry_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim lngIDNumber As Long
Set db = CurrentDb
Set rs = db.OpenRecordset("OrderEnquiry")
Set rs1 = db.OpenRecordset("OrderEnquiryDetails")

rs.AddNew
rs![SupplierID] = Me.lstSuppliers.Column(0) ****this enters the
correct information*****
rs![Description] = "Testing Order Enquiry Entries"
rs![Status] = "Created"
rs![Notes] = "This is great"
lngIDNumber = rs![OrderEnquiryID] 'Store number in a variable for use
later on
rs.update

For k = 0 To Me.lstPartsReq.ListCount - 1
rs1.AddNew
rs1![OEID] = lngIDNumber
rs1![PartID] = Me.lstPartsReq.Column(0) ****doesn't enter
nothing. On debug value is NULL****
rs1![LocationID] = Me.lstPartsReq.Column(7****doesn't enter nothing.
On debug value is NULL****
rs1![Description] = Me.lstPartsReq.Column(1)****doesn't enter
nothing. On debug value is NULL****
rs1![Quantity] = Me.lstPartsReq.Column(2) ****doesn't enter
nothing. On debug value is NULL****
rs1.update
Next

rs.Close
rs1.Close
End Sub


If you can help further i would be grateful
Thanks again
 
D

Deano

I've got it working now Kind of.

I've found that the lstpartreq listbox needs to have focus before it will
retreive any values. Also it tries to do it twice still.

Thanks
 
N

Nikos Yannacopoulos

Deano,

If the Me. keyword in the reference works correctly for the first listbox,
then the code is in the form's own module, not in a general module. Having
set this straight, please accept my apology for the oversight on my part,
the code snippet for the second recordset should be:

For k = 0 To Me.lstPartsReq.ListCount - 1
rs1.AddNew
rs1![OEID] = lngIDNumber
rs1![PartID] = Me.lstPartsReq.Column(0, k)
rs1![LocationID] = Me.lstPartsReq.Column(7, k)
rs1![Description] = Me.lstPartsReq.Column(1, k)
rs1![Quantity] = Me.lstPartsReq.Column(2, k)
rs1.Update
Next

Also note that headers will indeed be returned if the list box's Column
Heads property is set to yes. If that's the case, then:
if you do want them, the For scope should be:
For k = 0 To Me.lstPartsReq.ListCount
end the rest of the code as above.
If you don't (which I assume is your case), then you need to make provision
in your code to leavve it out. This would do it:
For k = 1 To Me.lstPartsReq.ListCount
again the rest of the code remains the same.

I should work now. Again, sorry for the inconvenience.

Nikos
 
D

Deano

Thanks for quick response, I should get it to work now

Thanks again for you help

Nikos Yannacopoulos said:
Deano,

If the Me. keyword in the reference works correctly for the first listbox,
then the code is in the form's own module, not in a general module. Having
set this straight, please accept my apology for the oversight on my part,
the code snippet for the second recordset should be:

For k = 0 To Me.lstPartsReq.ListCount - 1
rs1.AddNew
rs1![OEID] = lngIDNumber
rs1![PartID] = Me.lstPartsReq.Column(0, k)
rs1![LocationID] = Me.lstPartsReq.Column(7, k)
rs1![Description] = Me.lstPartsReq.Column(1, k)
rs1![Quantity] = Me.lstPartsReq.Column(2, k)
rs1.Update
Next

Also note that headers will indeed be returned if the list box's Column
Heads property is set to yes. If that's the case, then:
if you do want them, the For scope should be:
For k = 0 To Me.lstPartsReq.ListCount
end the rest of the code as above.
If you don't (which I assume is your case), then you need to make provision
in your code to leavve it out. This would do it:
For k = 1 To Me.lstPartsReq.ListCount
again the rest of the code remains the same.

I should work now. Again, sorry for the inconvenience.

Nikos

Deano said:
Thanks for the comments and response which is much appreciated I have
understood what you have said and suggestions but I am unable to get it to
work. You took the assumsion correct in that the code is a general module
under the OnClick button. Unfortunately it still inserts 2 blank
records(Details) even though there is only 1 record in the listbox. Here's
my new code

Private Sub cmdGenEnquiry_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim lngIDNumber As Long
Set db = CurrentDb
Set rs = db.OpenRecordset("OrderEnquiry")
Set rs1 = db.OpenRecordset("OrderEnquiryDetails")

rs.AddNew
rs![SupplierID] = Me.lstSuppliers.Column(0) ****this enters the
correct information*****
rs![Description] = "Testing Order Enquiry Entries"
rs![Status] = "Created"
rs![Notes] = "This is great"
lngIDNumber = rs![OrderEnquiryID] 'Store number in a variable for use
later on
rs.update

For k = 0 To Me.lstPartsReq.ListCount - 1
rs1.AddNew
rs1![OEID] = lngIDNumber
rs1![PartID] = Me.lstPartsReq.Column(0) ****doesn't enter
nothing. On debug value is NULL****
rs1![LocationID] = Me.lstPartsReq.Column(7****doesn't enter nothing.
On debug value is NULL****
rs1![Description] = Me.lstPartsReq.Column(1)****doesn't enter
nothing. On debug value is NULL****
rs1![Quantity] = Me.lstPartsReq.Column(2) ****doesn't enter
nothing. On debug value is NULL****
rs1.update
Next

rs.Close
rs1.Close
End Sub


If you can help further i would be grateful
Thanks again
Click to expand...
Click to expand...
 
D

Deano

Just to let you know, and anyone else who maybe looking at this post,the
code works apart from the headers are entered. Access enters the headers in
last so I manage to get round it with the following code

For k = 1 To Me.lstPartsReq.ListCount - 1
rs1.AddNew
rs1![OEID] = lngIDNumber
rs1![PartID] = Me.lstPartsReq.Column(0, k)
rs1![LocationID] = Me.lstPartsReq.Column(7, k)
rs1![Description] = Me.lstPartsReq.Column(1, k)
rs1![Quantity] = Me.lstPartsReq.Column(2, k)
rs1.update
Next

It puts the correct detail records in without entering any Headers from
listboxes etc.

Thanks again


Nikos Yannacopoulos said:
Deano,

If the Me. keyword in the reference works correctly for the first listbox,
then the code is in the form's own module, not in a general module. Having
set this straight, please accept my apology for the oversight on my part,
the code snippet for the second recordset should be:

For k = 0 To Me.lstPartsReq.ListCount - 1
rs1.AddNew
rs1![OEID] = lngIDNumber
rs1![PartID] = Me.lstPartsReq.Column(0, k)
rs1![LocationID] = Me.lstPartsReq.Column(7, k)
rs1![Description] = Me.lstPartsReq.Column(1, k)
rs1![Quantity] = Me.lstPartsReq.Column(2, k)
rs1.Update
Next

Also note that headers will indeed be returned if the list box's Column
Heads property is set to yes. If that's the case, then:
if you do want them, the For scope should be:
For k = 0 To Me.lstPartsReq.ListCount
end the rest of the code as above.
If you don't (which I assume is your case), then you need to make provision
in your code to leavve it out. This would do it:
For k = 1 To Me.lstPartsReq.ListCount
again the rest of the code remains the same.

I should work now. Again, sorry for the inconvenience.

Nikos

Deano said:
Thanks for the comments and response which is much appreciated I have
understood what you have said and suggestions but I am unable to get it to
work. You took the assumsion correct in that the code is a general module
under the OnClick button. Unfortunately it still inserts 2 blank
records(Details) even though there is only 1 record in the listbox. Here's
my new code

Private Sub cmdGenEnquiry_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim lngIDNumber As Long
Set db = CurrentDb
Set rs = db.OpenRecordset("OrderEnquiry")
Set rs1 = db.OpenRecordset("OrderEnquiryDetails")

rs.AddNew
rs![SupplierID] = Me.lstSuppliers.Column(0) ****this enters the
correct information*****
rs![Description] = "Testing Order Enquiry Entries"
rs![Status] = "Created"
rs![Notes] = "This is great"
lngIDNumber = rs![OrderEnquiryID] 'Store number in a variable for use
later on
rs.update

For k = 0 To Me.lstPartsReq.ListCount - 1
rs1.AddNew
rs1![OEID] = lngIDNumber
rs1![PartID] = Me.lstPartsReq.Column(0) ****doesn't enter
nothing. On debug value is NULL****
rs1![LocationID] = Me.lstPartsReq.Column(7****doesn't enter nothing.
On debug value is NULL****
rs1![Description] = Me.lstPartsReq.Column(1)****doesn't enter
nothing. On debug value is NULL****
rs1![Quantity] = Me.lstPartsReq.Column(2) ****doesn't enter
nothing. On debug value is NULL****
rs1.update
Next

rs.Close
rs1.Close
End Sub


If you can help further i would be grateful
Thanks again
Click to expand...
Click to expand...
 
N

Nikos Yannacopoulos

Deano,

Glad it's working. By the way, focus has nothing to do with it.

Nikos

Deano said:
Just to let you know, and anyone else who maybe looking at this post,the
code works apart from the headers are entered. Access enters the headers in
last so I manage to get round it with the following code

For k = 1 To Me.lstPartsReq.ListCount - 1
rs1.AddNew
rs1![OEID] = lngIDNumber
rs1![PartID] = Me.lstPartsReq.Column(0, k)
rs1![LocationID] = Me.lstPartsReq.Column(7, k)
rs1![Description] = Me.lstPartsReq.Column(1, k)
rs1![Quantity] = Me.lstPartsReq.Column(2, k)
rs1.update
Next

It puts the correct detail records in without entering any Headers from
listboxes etc.

Thanks again


Nikos Yannacopoulos said:
Deano,

If the Me. keyword in the reference works correctly for the first listbox,
then the code is in the form's own module, not in a general module. Having
set this straight, please accept my apology for the oversight on my part,
the code snippet for the second recordset should be:

For k = 0 To Me.lstPartsReq.ListCount - 1
rs1.AddNew
rs1![OEID] = lngIDNumber
rs1![PartID] = Me.lstPartsReq.Column(0, k)
rs1![LocationID] = Me.lstPartsReq.Column(7, k)
rs1![Description] = Me.lstPartsReq.Column(1, k)
rs1![Quantity] = Me.lstPartsReq.Column(2, k)
rs1.Update
Next

Also note that headers will indeed be returned if the list box's Column
Heads property is set to yes. If that's the case, then:
if you do want them, the For scope should be:
For k = 0 To Me.lstPartsReq.ListCount
end the rest of the code as above.
If you don't (which I assume is your case), then you need to make provision
in your code to leavve it out. This would do it:
For k = 1 To Me.lstPartsReq.ListCount
again the rest of the code remains the same.

I should work now. Again, sorry for the inconvenience.

Nikos

Deano said:
Thanks for the comments and response which is much appreciated I have
understood what you have said and suggestions but I am unable to get
Click to expand...
Click to expand...
it
to
work. You took the assumsion correct in that the code is a general module
under the OnClick button. Unfortunately it still inserts 2 blank
records(Details) even though there is only 1 record in the listbox. Here's
my new code

Private Sub cmdGenEnquiry_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim lngIDNumber As Long
Set db = CurrentDb
Set rs = db.OpenRecordset("OrderEnquiry")
Set rs1 = db.OpenRecordset("OrderEnquiryDetails")

rs.AddNew
rs![SupplierID] = Me.lstSuppliers.Column(0) ****this enters the
correct information*****
rs![Description] = "Testing Order Enquiry Entries"
rs![Status] = "Created"
rs![Notes] = "This is great"
lngIDNumber = rs![OrderEnquiryID] 'Store number in a variable for use
later on
rs.update

For k = 0 To Me.lstPartsReq.ListCount - 1
rs1.AddNew
rs1![OEID] = lngIDNumber
rs1![PartID] = Me.lstPartsReq.Column(0) ****doesn't enter
nothing. On debug value is NULL****
rs1![LocationID] = Me.lstPartsReq.Column(7****doesn't enter nothing.
On debug value is NULL****
rs1![Description] = Me.lstPartsReq.Column(1)****doesn't enter
nothing. On debug value is NULL****
rs1![Quantity] = Me.lstPartsReq.Column(2) ****doesn't enter
nothing. On debug value is NULL****
rs1.update
Next

rs.Close
rs1.Close
End Sub


If you can help further i would be grateful
Thanks again
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

Drop Down doesn't populate form 0
compile error: For control variable already in use 1
Microsoft Access has encountered a problem 0
trouble printing variavel 1
Selecting record from unbound box 2
Recordset Help 10
Bookmark Issue 7
Timer event does not fire 1

Top