Automatically Append New Blank Row/Line/Record in Subform (Bounded Form)

[Bruce M. Thompson]

Now User Input Voucher # in Main Form with Date and after filling other

field on main form User records MULTIPLE SERVICES RENDERED
in Subform. As the user press any Single Key in first record of
Subform A NEW BLANK Record/Line/Row is Appended in the subform.

I know that this is DEFAULT behavior of MS ACCESS table. But I want
to bypass this behavior. IN SUBFORMS Pressing any key SHOULD NOT
APPEND NEW BLANK RECORD in subform. New Row/Line/Record
SHOULD ONLY be appended when user press any specific button.

Hope this time I will get a reply with sample code.

If I am reading your correctly, that "new blank record" that you are referring
to is merely a placeholder for creating a new record - there is no record
present in that row. If this was not there you could not add a new record (you
can remove this placeholder if you set the form's "AllowAdditions" property to
"No", but ... surprise ... this will prevent you from adding any new records).

 

[Guest]

This is exactly the question I have posted over the last few days. So, how do you get around that blank record? The record I get includes the pk as well as two fks from the table the main form is based on, but empty fields for the data I wish to enter from the subform. So I end up with one record as described and then multiple records with no data (fk) from the main form and the data I enter from the subform. I am populating programatically the main form data into the records missing that, but what do I do with that other record?? Any assistance will be greatly appreciated!

----- Bruce M. Thompson wrote: ----

Now User Input Voucher # in Main Form with Date and after filling othe
field on main form User records MULTIPLE SERVICES RENDERE
in Subform. As the user press any Single Key in first record o
Subform A NEW BLANK Record/Line/Row is Appended in the subform
to bypass this behavior. IN SUBFORMS Pressing any key SHOULD NO
APPEND NEW BLANK RECORD in subform. New Row/Line/Recor
SHOULD ONLY be appended when user press any specific button

If I am reading your correctly, that "new blank record" that you are referrin
to is merely a placeholder for creating a new record - there is no recor
present in that row. If this was not there you could not add a new record (yo
can remove this placeholder if you set the form's "AllowAdditions" property t
"No", but ... surprise ... this will prevent you from adding any new records)

 

[Bruce M. Thompson]

This is exactly the question I have posted over the last few days.

So, how do you get around that blank record?

The blank row I am referring to does not contain any data, therefore it is not a
"blank record", but rather a placeholder for a new record.

The record I get
includes the pk as well as two fks from the table the main form is
based on, but empty fields for the data I wish to enter from the
subform.

If the primary key field in the subform is an autonumber type and contains a
value, then a record in that row has been started. If it is not an autonumber,
is it possible that there are "default" values set for those controls in the
design of the form? If that is the case, and those default values are equal to
the the values of "the pk as well as two fks from the table the main form is
based on" then I would presume that this row is, too, just a placeholder. If the
subform's record selectors are turned on and the record selector for this
"blank" row contains an asterisk (*), then it is merely a placeholder and not an
actual record.

So I end up with one record as described and then multiple
records with no data (fk) from the main form and the data I enter
from the subform. I am populating programatically the main form data
into the records missing that, but what do I do with that other record??

If you are ending up with multiple records in your subform (per main form
record) that contain blank fields then I would look at your code for problems.
If you want someone else to look at the code for/with you, simply post the code
for others to review and the source of your problem may be brought to light.

 

[Guest]

Thanks Bruce,

I appreciate any help you or anyone else can give me on this.

Robbie

tblToFromCop
Id Autonumber p
IOCYear f
IOCNumber f
ContactID f
ContactTypeID f

tlkpContact
ContactID Autonumber p
FirstNam
LastNam
etc.

tlkpContactTyp
ContactTypeID Autonumber p
ContactTyp

tblIOCLo
IOCYear p
IOCNumber p
IOCDat
Subjec
etc.

Subforms sfmTo and sfmCopy (record source – tblToFromCopy) have a multiselect listbox to choose values from tlkpContacts
Form fmDataEntry has a record source of tblIOCLo

Subform sfmFrom (record source – tblToFromCopy) has a listbox (not multiselect) and a hidden textbox with default value from tlkpContactType.

My code:

Private Sub Form_Open(Cancel As Integer
DoCmd.GoToRecord , , acNewRe
Me.IOCYear = Year(Date
Me.IOCNumber = Nz(DMax("[IOCNumber]", "[tblIOCLog]", ""), 0) +
Me.IOCDate = Dat
End Su


Private Sub sfmCopy_Exit(Cancel As Integer

Dim db As DAO.Databas
Dim rst As DAO.Recordse
Dim i As Intege

Set db = CurrentDb(
Set rst = db.OpenRecordset("tblToFromCopy", DB_OPEN_DYNASET

For i = 0 To Form_sfmCopy.lstCC.ListCount -
If Form_sfmCopy.lstCC.Selected(i) The
rst.AddNe
rst!IOCYear = Me.IOCYea
rst!IOCNumber = Me.IOCNumbe
rst!ContactID = Form_sfmCopy.lstCC.Column(0, i
rst!ContactTypeID =
rst.Updat
'Form_sfmCopy.lstCC.Selected(i) = False 'clears the selectio
End I
Next

End Su

Private Sub sfmTo_Exit(Cancel As Integer

Dim db As DAO.Databas
Dim rst As DAO.Recordse
Dim i As Intege

Set db = CurrentDb(
Set rst = db.OpenRecordset("tblToFromCopy", DB_OPEN_DYNASET

For i = 0 To Form_sfmTo.lstTo.ListCount -

If Form_sfmTo.lstTo.Selected(i) The
rst.AddNe
rst!IOCYear = Me.IOCYea
rst!IOCNumber = Me.IOCNumbe
rst!ContactID = Form_sfmTo.lstTo.Column(0, i
rst!ContactTypeID =
rst.Updat
'Form_sfmTo.lstTo.Selected(i) = False 'clears the selectio
End I
Next

End Su

Sample values I get in tblToFromCopy when one value is selected from each subform AND I don’t set IOCYear and IOCNumber in the multiselect code above (I expect 3 records):

1st record 2nd record 3rd record 4th recor
I
IOCYear 2004
IOCNumber 1
ContactID 12 13 2
ContactTypeID 1 2

tblIOCLog will have a record IOCYear 2004, IOCNumber 1 .


----- Bruce M. Thompson wrote: ----

This is exactly the question I have posted over the last few days
So, how do you get around that blank record

The blank row I am referring to does not contain any data, therefore it is not
"blank record", but rather a placeholder for a new record

The record I ge
includes the pk as well as two fks from the table the main form i
based on, but empty fields for the data I wish to enter from th
subform

If the primary key field in the subform is an autonumber type and contains
value, then a record in that row has been started. If it is not an autonumber
is it possible that there are "default" values set for those controls in th
design of the form? If that is the case, and those default values are equal t
the the values of "the pk as well as two fks from the table the main form i
based on" then I would presume that this row is, too, just a placeholder. If th
subform's record selectors are turned on and the record selector for thi
"blank" row contains an asterisk (*), then it is merely a placeholder and not a
actual record.

So I end up with one record as described and then multiple
records with no data (fk) from the main form and the data I enter
from the subform. I am populating programatically the main form data
into the records missing that, but what do I do with that other record??

If you are ending up with multiple records in your subform (per main form
record) that contain blank fields then I would look at your code for problems.
If you want someone else to look at the code for/with you, simply post the code
for others to review and the source of your problem may be brought to light.

 

[Bruce M. Thompson]

My code:

Private Sub Form_Open(Cancel As Integer)
DoCmd.GoToRecord , , acNewRec
Me.IOCYear = Year(Date)
Me.IOCNumber = Nz(DMax("[IOCNumber]", "[tblIOCLog]", ""), 0) + 1
Me.IOCDate = Date
End Sub

Why are you adding a record in the form's Open event? Do you really want to add
a new record every time the form is opened? This could be the cause of your
problem. If you want to insert this information only when you are starting a
child record you should use the "BeforeInsert" event, instead. Is there
something that I'm not seeing here?

I'm not even sure if the code appearing above is related to your problem,
however. I only need to see the code relevant to your "blank rows" problem. If
the above *is* the relevant code, then let me know (and tell me if I'm not on
the right track).

 

[Syed Ubaid]

Hi,

//// I got reply of below question but couldn't make around. /////

I designed a Hospital Billing System. Its working fine but during the
developmentof this project I faced a problem which I also posted
on this Newsgroup, however couldn't get any solution. The scenario
is as follows:

SCENARIO:

Main & SubForms are bound to tables with 1 to Many relationship.This
Form (Subform) records SERVICES rendered on One Voucher. On a
single voucher 1 or more services may be rendered.

Now User Input Voucher # in Main Form with Date and after filling other
field on main form User records MULTIPLE SERVICES RENDERED
in Subform. As the user press any Single Key in first record of
Subform A NEW BLANK Record/Line/Row is Appended in the subform.

I know that this is DEFAULT behavior of MS ACCESS table. But I want
to bypass this behavior. IN SUBFORMS Pressing any key SHOULD NOT
APPEND NEW BLANK RECORD in subform. New Row/Line/Record
SHOULD ONLY be appended when user press any specific button.

Hope this time I will get a reply with sample code.

Thanks

SYED

 

[Bruce M. Thompson]

Response below.

Thank you Bruce for working through this with me. I changed this to the

BeforeInsert Event and found that I am still getting the incomplete record. I
get one for each subform with multiselect listbox, so you are right, the OnOpen
code was not related. The code for the subforms follows. Do you see anything
that could cause problems, or is there anything about how forms and subforms
work that I am missing? Please bear with me as I am very new at this. Thanks!

Private Sub sfmCopy_Exit(Cancel As Integer)
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim i As Integer
Set db = CurrentDb()
Set rst = db.OpenRecordset("tblToFromCopy", DB_OPEN_DYNASET)
For i = 0 To Form_sfmCopy.lstCC.ListCount - 1
If Form_sfmCopy.lstCC.Selected(i) Then
rst.AddNew
rst!IOCYear = Me.IOCYear
rst!IOCNumber = Me.IOCNumber
rst!ContactID = Form_sfmCopy.lstCC.Column(0, i)
rst!ContactTypeID = 3
rst.Update
'Form_sfmCopy.lstCC.Selected(i) = False 'clears the selection
End If
Next i
End Sub

This code appears to be okay - it, alone, won't generate a "blank record". I
don't see anything in the event you have chosen that would generate such a
behavior, either. I would guess that your problem exists somewhere else.

Just a quick thought ... if you select a blank record and try to delete it, do
you get an error or does the record get deleted?

 

[Guest]

Good morning Bruce -

I can delete a partial record with no error messages. Another thing I just tried is to set ContactID to "Required". As soon as I mouse-click a selection in the list box of the subform I get the error "The field 'tblToFromCopy.ContactID' cannot contain a null value because the required property for this field is set to true"

The same thing happens if I work with the subform independently of the main form.
The listbox in the subform has a rowsource:
SELECT [qryContactList].[ContactID], [qryContactList].[FirstName], [qryContactList].[LastName], [qryContactList].[Contact], [qryContactList].[Location] FROM qryContactList;
and control source Contact.ID. I am using this query to sort on LastName.

Do you see anything amiss here?

Thanks!
I'm using this query to sort on LastName

----- Bruce M. Thompson wrote: -----

Response below.

Thank you Bruce for working through this with me. I changed this to the

BeforeInsert Event and found that I am still getting the incomplete record. I
get one for each subform with multiselect listbox, so you are right, the OnOpen
code was not related. The code for the subforms follows. Do you see anything
that could cause problems, or is there anything about how forms and subforms
work that I am missing? Please bear with me as I am very new at this. Thanks!

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim i As Integer
Set db = CurrentDb()
Set rst = db.OpenRecordset("tblToFromCopy", DB_OPEN_DYNASET)
For i = 0 To Form_sfmCopy.lstCC.ListCount - 1
If Form_sfmCopy.lstCC.Selected(i) Then
rst.AddNew
rst!IOCYear = Me.IOCYear
rst!IOCNumber = Me.IOCNumber
rst!ContactID = Form_sfmCopy.lstCC.Column(0, i)
rst!ContactTypeID = 3
rst.Update
'Form_sfmCopy.lstCC.Selected(i) = False 'clears the selection
End If
Next i
End Sub

This code appears to be okay - it, alone, won't generate a "blank record". I
don't see anything in the event you have chosen that would generate such a
behavior, either. I would guess that your problem exists somewhere else.

Just a quick thought ... if you select a blank record and try to delete it, do
you get an error or does the record get deleted?

 

[Bruce M. Thompson]

I can delete a partial record with no error messages.

??? Please clarify what you mean here by "delete a partial record".

Another thing I just tried is to set ContactID to "Required". As soon as I

mouse-click a selection in the list box of the subform I get the error "The
field 'tblToFromCopy.ContactID' cannot contain a null value because the required
property for this field is set to true"

What field is the listbox bound to? Is there more than one table in the form's
recordsource? You might want to post the form's recordsource to help with the
"diagnosis" of your problem.

The same thing happens if I work with the subform independently of the main form.
The listbox in the subform has a rowsource:
SELECT [qryContactList].[ContactID], [qryContactList].[FirstName],

[qryContactList].[LastName], [qryContactList].[Contact],
[qryContactList].[Location] FROM qryContactList;

and control source Contact.ID. I am using this query to sort on LastName.

You are using this query to sort what on LastName? That sql statement is
certainly not sorted by that field.

 

[Guest]

Can anyone see where I am making errors? I appreciate all the help so far, but I haven't solved the problem yet. Thanks!

----- Robbie M wrote: ----

I meant that I can delete the record that pulls IOCYear and IOCNumber from the main form, but not the other two fields from the subform.

The listbox is bound to ContactID. Its row source: SELECT [qryContactList].[ContactID], [qryContactList].[FirstName]
[qryContactList].[LastName], [qryContactList].[Contact]
[qryContactList].[Location] FROM qryContactList

The query qryContactList that sorts on the field LastName: SELECT tlkpContacts.ContactID, tlkpContacts.FirstName, tlkpContacts.LastName, Left([tlkpContacts].[FirstName],1) & " " & [tlkpContacts].[LastName] AS Contact, tlkpLocation.Locatio
FROM tlkpLocation INNER JOIN tlkpContacts ON tlkpLocation.LocationID = tlkpContacts.LocationI
ORDER BY tlkpContacts.LastName

The subform has only tblToFromCopy for the record source with fields

ID pk;autonumbe
IOCYear
IOCNumber
ContactI
ContactTypeID

Thanks!


----- Bruce M. Thompson wrote: ----

I can delete a partial record with no error messages

??? Please clarify what you mean here by "delete a partial record"

Another thing I just tried is to set ContactID to "Required". As soon as

mouse-click a selection in the list box of the subform I get the error "Th
field 'tblToFromCopy.ContactID' cannot contain a null value because the require
property for this field is set to true

What field is the listbox bound to? Is there more than one table in the form'
recordsource? You might want to post the form's recordsource to help with th
"diagnosis" of your problem

The same thing happens if I work with the subform independently of the mai

form
The listbox in the subform has a rowsource
SELECT [qryContactList].[ContactID], [qryContactList].[FirstName]

[qryContactList].[LastName], [qryContactList].[Contact]
[qryContactList].[Location] FROM qryContactList

and control source Contact.ID. I am using this query to sort on LastName

You are using this query to sort what on LastName? That sql statement i
certainly not sorted by that field

 

[Bruce M. Thompson]

I will try to get to this by tomorrow (sorry if this is a problem). I have been
tied up since yesterday afternoon and need to attend to these matters first.

 

[Guest]

That would be great Bruce. Thanks

----- Bruce M. Thompson wrote: ----

I will try to get to this by tomorrow (sorry if this is a problem). I have bee
tied up since yesterday afternoon and need to attend to these matters first

:-

 

[Guest]

One other piece of information that may or may not be relevant - the query qryContactList (sorts on field tlkpContacts.LastName) is based on a table tlkpContacts that has a lookup field LocationID.

----- Robbie M wrote: ----

That would be great Bruce. Thanks

----- Bruce M. Thompson wrote: ----

I will try to get to this by tomorrow (sorry if this is a problem). I have bee
tied up since yesterday afternoon and need to attend to these matters first

:-

 

[Bruce M. Thompson]

Responses interspersed.

I meant that I can delete the record that pulls IOCYear and IOCNumber from the

main form, but not the other two fields from the subform.

**<slightly confused here> Have you tried to use the menu item "Edit, Delete
Record" to delete the record? Deleting the contents of the fields does not
delete the record.

The listbox is bound to ContactID. Its row source: SELECT

[qryContactList].[ContactID], [qryContactList].[FirstName],

[qryContactList].[LastName], [qryContactList].[Contact],
[qryContactList].[Location] FROM qryContactList;

**This rowsource seems fine. I'm wondering, however, how your "multiselect"
listboxes come into this picture (you mentioned these earlier in the thread).

The query qryContactList that sorts on the field LastName: SELECT

tlkpContacts.ContactID, tlkpContacts.FirstName, tlkpContacts.LastName,
Left([tlkpContacts].[FirstName],1) & " " & [tlkpContacts].[LastName] AS Contact,
tlkpLocation.Location

FROM tlkpLocation INNER JOIN tlkpContacts ON tlkpLocation.LocationID = tlkpContacts.LocationID
ORDER BY tlkpContacts.LastName;

**You haven't said where this query is used.

The subform has only tblToFromCopy for the record source with fields

ID pk;autonumber
IOCYear
IOCNumber
ContactID
ContactTypeID

**Well, obviously there is only one table in the subform's recordsource, so I
wonder if your problem lies in the way you are deleting your records (see my
first response, above).

 

[Guest]

My responses interspersed also - thanks Bruce.

----- Bruce M. Thompson wrote: ----

Responses interspersed

I meant that I can delete the record that pulls IOCYear and IOCNumber from th

main form, but not the other two fields from the subform

**<slightly confused here> Have you tried to use the menu item "Edit, Delet
Record" to delete the record? Deleting the contents of the fields does no
delete the record
##### I understand your confusion. I can delete the offending record. It's just that earlier in the thread I called it a blank record and of course it is not. It does have IOCYear and IOCNumber from the main form, but does not pull the ContactID from the subform. (I mis-spoke earlier - the record will have a 0 in the ContactType field. ) But, I have no problem deleting the record. #####

The listbox is bound to ContactID. Its row source: SELEC

[qryContactList].[ContactID], [qryContactList].[FirstName]

[qryContactList].[LastName], [qryContactList].[Contact]
[qryContactList].[Location] FROM qryContactList

**This rowsource seems fine. I'm wondering, however, how your "multiselect
listboxes come into this picture (you mentioned these earlier in the thread)
####This listbox is the multi-select listbox####

The query qryContactList that sorts on the field LastName: SELEC

tlkpContacts.ContactID, tlkpContacts.FirstName, tlkpContacts.LastName
Left([tlkpContacts].[FirstName],1) & " " & [tlkpContacts].[LastName] AS Contact
tlkpLocation.Locatio

FROM tlkpLocation INNER JOIN tlkpContacts ON tlkpLocation.LocationID tlkpContacts.LocationI
ORDER BY tlkpContacts.LastName

**You haven't said where this query is used

#####This query is used as a rowsource for the multi-select listbox above. #####

The subform has only tblToFromCopy for the record source with field
IOCYea
IOCNumbe
ContactI
ContactTypeI

**Well, obviously there is only one table in the subform's recordsource, so
wonder if your problem lies in the way you are deleting your records (see m
first response, above)

####I do not routinely delete the record I don't want. I don't know why that record is being created and would like to prevent it. Thanks!####

 

[Bruce M. Thompson]

My responses interspersed also - thanks Bruce.

**No problem.

----- Bruce M. Thompson wrote: -----

Responses interspersed.
from the
main form, but not the other two fields from the subform.

**<slightly confused here> Have you tried to use the menu item "Edit, Delete
Record" to delete the record? Deleting the contents of the fields does not
delete the record.
##### I understand your confusion. I can delete the offending record. It's

just that earlier in the thread I called it a blank record and of course it is
not. It does have IOCYear and IOCNumber from the main form, but does not pull
the ContactID from the subform.

**I thought this *was* the subform.

(I mis-spoke earlier - the record will have a 0 in the ContactType field. )

But, I have no problem deleting the record. ######

**Hmmmm ... sounds like a new record placeholder to me, not a blank record. So I
don't know why you can delete it. I presume that you've looked at the table to
verify that all is as it appears?

The listbox is bound to ContactID. Its row source: SELECT

[qryContactList].[ContactID], [qryContactList].[FirstName],

[qryContactList].[LastName], [qryContactList].[Contact],
[qryContactList].[Location] FROM qryContactList;

**This rowsource seems fine. I'm wondering, however, how your "multiselect"
listboxes come into this picture (you mentioned these earlier in the thread).
####This listbox is the multi-select listbox#####

**If you are expecting the ContactID field to contain a value in this case, you
are mistaken. A bound multi-select listbox will always contain a "Null" value,
hence the ContactID field will inherit this value.

The query qryContactList that sorts on the field LastName: SELECT

tlkpContacts.ContactID, tlkpContacts.FirstName, tlkpContacts.LastName,
Left([tlkpContacts].[FirstName],1) & " " & [tlkpContacts].[LastName] AS Contact,

tlkpLocation.Location
FROM tlkpLocation INNER JOIN tlkpContacts ON tlkpLocation.LocationID = tlkpContacts.LocationID
ORDER BY tlkpContacts.LastName;

**You haven't said where this query is used.

#####This query is used as a rowsource for the multi-select listbox above.

######

**So, you are assigning this as the rowsource for the listbox via code? I'm
asking this becuase you appear to be telling me that you have 2 different
rowsource strings for the same listbox.

**Well, obviously there is only one table in the subform's recordsource, so I
wonder if your problem lies in the way you are deleting your records (see my
first response, above).

####I do not routinely delete the record I don't want. I don't know why that

record is being created and would like to prevent it. Thanks!#####

**Is your code generating *any* records that meet your requirements? One more
thing - are there any "Default Value" settings for the "IOCYear" or "IOCNumber"
fields in either the form's design?

 

[Bruce M. Thompson]

The main form supplies IOCYear and IOCNumber. The subform should supply

ContactID and ContactType. I use a multiselect listbox with code to store
ContactID and ContactType in this same table - tblToFromCopy. The code is
working and does store all the records I select in the multiselect listbox.
However, the first record it stores is one I do not select: the one with
IOCYear and IOCNumber and a 0 value in ContactType. I do not want this record
and I do not understand why it is being created. It stores this first record at
the time I make the first selection in the listbox. I know this because if I
set ContactID to required, I get an error about ContactID having a null value.


**If your multi-select listbox is bound to the ContactID field, clicking on it
will force a Null into the ContactID field. See my other comment, below.

The multiselect listbox has a rowsource of
SELECT

tlkpContacts.ContactID, tlkpContacts.FirstName, tlkpContacts.LastName,
Left([tlkpContacts].[FirstName],1) & " " &

[tlkpContacts].[LastName] AS
Contact,

tlkpLocation.Location

The control source is tblToFromCopy.ContactID.

**Again, if your listbox is multi-select, it serves no purpose to bind it to any
field. As soon as you click on the listbox, it will force a Null value into the
field, even if it contained a value prior to the click. A multi-select listbox
with a selected value can contain only a Null. I think this is the source of
your unwanted record.

 

[Guest]

Thank you Bruce. I really appreciate the time you've taken to look at this for me. I just want to make sure I have this right. I do have code to store the selected values (see below) - but what you are saying is that before this code runs, a record is being created with a null value for ContactID right? So in general whenever I need to store values from a multiselect listbox I would need to delete the current record at some event? What would be the best event to use to delete that record?

Private Sub sfmTo_Exit(Cancel As Integer

Dim db As DAO.Databas
Dim rst As DAO.Recordse
Dim i As Intege

Set db = CurrentDb(
Set rst = db.OpenRecordset("tblToFromCopy", DB_OPEN_DYNASET

For i = 0 To Form_sfmTo.lstTo.ListCount -

If Form_sfmTo.lstTo.Selected(i) The
rst.AddNe
rst!IOCYear = Me.IOCYea
rst!IOCNumber = Me.IOCNumbe
rst!ContactID = Form_sfmTo.lstTo.Column(0, i
rst!ContactTypeID =
rst.Updat
'Form_sfmTo.lstTo.Selected(i) = False 'clears the selectio
End I
Next

End Su

----- Bruce M. Thompson wrote: ----

The main form supplies IOCYear and IOCNumber. The subform should suppl

ContactID and ContactType. I use a multiselect listbox with code to stor
ContactID and ContactType in this same table - tblToFromCopy. The code i
working and does store all the records I select in the multiselect listbox
However, the first record it stores is one I do not select: the one wit
IOCYear and IOCNumber and a 0 value in ContactType. I do not want this recor
and I do not understand why it is being created. It stores this first record a
the time I make the first selection in the listbox. I know this because if
set ContactID to required, I get an error about ContactID having a null value


**If your multi-select listbox is bound to the ContactID field, clicking on i
will force a Null into the ContactID field. See my other comment, below

The multiselect listbox has a rowsource o SELEC
tlkpContacts.ContactID, tlkpContacts.FirstName tlkpContacts.LastName
Left([tlkpContacts].[FirstName],1) & " "

[tlkpContacts].[LastName] A
Contact

tlkpLocation.Locatio
The control source is tblToFromCopy.ContactID

**Again, if your listbox is multi-select, it serves no purpose to bind it to an
field. As soon as you click on the listbox, it will force a Null value into th
field, even if it contained a value prior to the click. A multi-select listbo
with a selected value can contain only a Null. I think this is the source o
your unwanted record

 

[Bruce M. Thompson]

Thank you Bruce. I really appreciate the time you've taken to look at this

for me. I just want to make sure I have this right. I do have code to store
the selected values (see below) - but what you are saying is that before this
code runs, a record is being created with a null value for ContactID right? So
in general whenever I need to store values from a multiselect listbox I would
need to delete the current record at some event? What would be the best event
to use to delete that record?
Actually, clearing the multi-select listbox's "Control Source" property should
take care of that. Remember, a multi-select listbox should be bound to NO field.
If this doesn't take care of the problem, then there must be something else that
I'm not aware of going on.

 

[Guest]

Thank you! That did it!

Robbie