Using an unbound combo box to populate fields

[Guest]

I am using an unbound combo box to populate fields on a form. The "After
Update" event procedure is:

Private Sub cmbRRepeat_AfterUpdate()

Me!COMPANYNAME = Me![cmbRRepeat].Column(1)
Me!ADDRESS1 = Me![cmbRRepeat].Column(2)
Me!ADDRESS2 = Me![cmbRRepeat].Column(3)
Me!CITY = Me![cmbRRepeat].Column(4)
Me!STATE = Me![cmbRRepeat].Column(5)
Me!ZIP = Me![cmbRRepeat].Column(6)

End Sub

My question is....in some of the records the ADDRESS2 is blank. Whenever I
select any of the records where ADDRESS2 is blank, I get a
Runtime error 3315
Field tblResource.ADDRESS2 cannot be a zero length string

Any advise would be greatly appreciated.

Rhonda Marko
(e-mail address removed)

 

[Tom Ellison]

Dear R:

In the design of the table being updated, does ADDRESS2 allow nulls, or zero
length? If your design specifies it cannot be a zero length string, then,
well, it cannot be a zero length string.

Tom Ellison

 

[Guest]

Before I can offer any help here I need to know what your intent is. Are you
trying to view an existing record based on the data in your Combo, or are you
trying to create a new record?

If you are trying to create a new record, then you should write some code
that executes after the Combo is updated. That code should write a new
record to your recordset. For each field that will be updated, the source
data variable should be checked for a Null or Zero Len value.

Example:

Recordset.AddNew

If(len(nz(MyVariable,"")>0) then
Recordset!Field(idx)=MyVariable
intUpdated = intUpdated + 1
end if

if(intUpdated>0) then
Recordset.Update
End if

Now if you are trying show a particular record on your form based on the
Combo then you might consider using the form's filter.

Example:
Private Sub MyCombo_AfterUpdate()
Me.Filter = "MyFieldName = " & MyCombo
Me.FilterOn = True
End Sub

 

[Guest]

You're a genius....one more quick question, if you don't mind

After populating the form with the new information, populating other fields
in the form and opening a new record, the combo box still displays the
information from the previous entry. How do I get the combo box to display
nothing after leaving the updated record?

Dear R:

In the design of the table being updated, does ADDRESS2 allow nulls, or zero
length? If your design specifies it cannot be a zero length string, then,
well, it cannot be a zero length string.

Tom Ellison

I am using an unbound combo box to populate fields on a form. The "After
Update" event procedure is:

Private Sub cmbRRepeat_AfterUpdate()

Me!COMPANYNAME = Me![cmbRRepeat].Column(1)
Me!ADDRESS1 = Me![cmbRRepeat].Column(2)
Me!ADDRESS2 = Me![cmbRRepeat].Column(3)
Me!CITY = Me![cmbRRepeat].Column(4)
Me!STATE = Me![cmbRRepeat].Column(5)
Me!ZIP = Me![cmbRRepeat].Column(6)

End Sub

My question is....in some of the records the ADDRESS2 is blank. Whenever
I
select any of the records where ADDRESS2 is blank, I get a
Runtime error 3315
Field tblResource.ADDRESS2 cannot be a zero length string

Any advise would be greatly appreciated.

Rhonda Marko
(e-mail address removed)

 

[Guest]

The table is an address book. We found that whenever we made an addition to
the address book, we had to retype the "company name" "address" "city"
"state" and "zip" Since many of the entries are multiple contact from the
same company, I created a combo box with the fields that would typically
repeat.

Now, to make an entry, we choose the name of the company from the combo box
which then populates those fields listed above....and we're ready to enter
the name, phone and email address of the new contact. It now works great,
however, when I "go to new" record to make a new entry, the combo box still
contains the information from the last entry.

Is there an event procedure I can write in "Upon Exit" or "After Update"
where after the combo box populates the fields in the form, it goes blank?

Before I can offer any help here I need to know what your intent is. Are you
trying to view an existing record based on the data in your Combo, or are you
trying to create a new record?

If you are trying to create a new record, then you should write some code
that executes after the Combo is updated. That code should write a new
record to your recordset. For each field that will be updated, the source
data variable should be checked for a Null or Zero Len value.

Example:

Recordset.AddNew

If(len(nz(MyVariable,"")>0) then
Recordset!Field(idx)=MyVariable
intUpdated = intUpdated + 1
end if

if(intUpdated>0) then
Recordset.Update
End if

Now if you are trying show a particular record on your form based on the
Combo then you might consider using the form's filter.

Example:
Private Sub MyCombo_AfterUpdate()
Me.Filter = "MyFieldName = " & MyCombo
Me.FilterOn = True
End Sub

I am using an unbound combo box to populate fields on a form. The "After
Update" event procedure is:

Private Sub cmbRRepeat_AfterUpdate()

Me!COMPANYNAME = Me![cmbRRepeat].Column(1)
Me!ADDRESS1 = Me![cmbRRepeat].Column(2)
Me!ADDRESS2 = Me![cmbRRepeat].Column(3)
Me!CITY = Me![cmbRRepeat].Column(4)
Me!STATE = Me![cmbRRepeat].Column(5)
Me!ZIP = Me![cmbRRepeat].Column(6)

End Sub

My question is....in some of the records the ADDRESS2 is blank. Whenever I
select any of the records where ADDRESS2 is blank, I get a
Runtime error 3315
Field tblResource.ADDRESS2 cannot be a zero length string

Any advise would be greatly appreciated.

Rhonda Marko
(e-mail address removed)