Why is my data in my table getting erased

[Guest]

I have a form that has an unbound combo box that fills in Text boxes fields.
The problem I have is that the first record after the second search is
getting erased in the Table. When I actually go into the table the first
record is blanked out. It's almost as if something is overwriting the fields.

What is causing this?

 

[John Vinson]

I have a form that has an unbound combo box that fills in Text boxes fields.
The problem I have is that the first record after the second search is
getting erased in the Table. When I actually go into the table the first
record is blanked out. It's almost as if something is overwriting the fields.

What is causing this?

What's overwriting the fields is... your combo box!

Please post your code, and the Control Source properties of the
relevant fields. If you copy data from a combo box into a bound
textbox, it will indeed replace whatever was previously there; if your
combo box is in fact bound, selecting a value will overwrite the
current record's value for that field.

John W. Vinson[MVP]

 

[Guest]

Below is the code and the query. Just a note that this form is for Viewing
only there is no updating going on. I'll do that on another Form.

CTAName in the Query is my Combo Box.

In the Control Source Listings:
Control Source is empty, The Combo Box is mark as Unbound, Bound = 3, Limit
to List=Y, Auto Expand=Y, Enable=Y, Locked=N, Allow Auto Correct=N, Display
When=Always. I have a Macro on Click called mcrCTAMainTextBox and what this
does is to SetValue in the textboxes on the form.

The Code:
Private Sub CTAName_Combo_AfterUpdate()

Dim ctl As Control
Dim frm As Form
Set frm = Forms!CTAMainView
With frm
For Each ctl In frm
If ctl.ControlType = acTextBox Then
ctl.Value = ""
End If
Next
End With

End Sub

Private Sub CTAAddress_Text_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub Form_Load()

Me.CTAAddress_Text = Null
Me.CTACity_Text = Null
Me.CTAState_Text = Null
Me.CTAZIP_Text = Null
Me.CTAPhone_Text = Null
Me.CTAFax_Text = Null
Me.CTAEmail_Text = Null
Me.CTARegion_Text = Null
Me.CTAEligibility_Text = Null
Me.Reopening1_Text = Null
Me.Reopening2_Text = Null
Me.CName1_Text = Null
Me.CPhone1_Text = Null
Me.CFax1_Text = Null
Me.CEmail1_Text = Null

End Sub
Private Sub Additional_Contacts_Click()
On Error GoTo Err_Additional_Contacts_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frm_CTAContactsView"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Additional_Contacts_Click:
Exit Sub

Err_Additional_Contacts_Click:
MsgBox Err.Description
Resume Exit_Additional_Contacts_Click

End Sub

Private Sub Products_Click()
On Error GoTo Err_Products_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frm_CTAProductsView"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Products_Click:
Exit Sub

Err_Products_Click:
MsgBox Err.Description
Resume Exit_Products_Click

End Sub


SELECT DISTINCTROW qry_CTAMainTest.CTAName, qry_CTAMainTest.Number,
qry_CTAMainTest.Address, qry_CTAMainTest.CTACity, qry_CTAMainTest.CTAState,
qry_CTAMainTest.Zip, qry_CTAMainTest.CTAPhone, qry_CTAMainTest.CTAFax,
qry_CTAMainTest.CTAEmail, qry_CTAMainTest.CTARegion,
qry_CTAMainTest.CTAEligibility, qry_CTAMainTest.Reopening1,
qry_CTAMainTest.Reopening2, qry_CTAMainTest.CTAContactName1,
qry_CTAMainTest.CTAContactPhone1, qry_CTAMainTest.CTAContactFax1,
qry_CTAMainTest.CTAContactEmail1, qry_CTAMainTest.CTAContactName2,
qry_CTAMainTest.CTAContactPhone2, qry_CTAMainTest.CTAContactFax2,
qry_CTAMainTest.CTAContactEmail2, qry_CTAMainTest.CTAContactName3,
qry_CTAMainTest.CTAContactPhone3, qry_CTAMainTest.CTAContactFax3,
qry_CTAMainTest.CTAContactEmail3, qry_CTAMainTest.CTAContactName4,
qry_CTAMainTest.CTAContactPhone4, qry_CTAMainTest.CTAContactFax4,
qry_CTAMainTest.CTAContactEmail4 FROM qry_CTAMainTest;

 

[John Vinson]

Private Sub CTAName_Combo_AfterUpdate()

Dim ctl As Control
Dim frm As Form
Set frm = Forms!CTAMainView
With frm
For Each ctl In frm
If ctl.ControlType = acTextBox Then
ctl.Value = ""
End If
Next
End With

End Sub

Well? There's your answer!

This code specifically erases every textbox (bound or unbound) on the
form, blanking out the table data for each bound textbox.

What was your *intent* with this event?


John W. Vinson[MVP]

 

[Guest]

On the first view everything works fine. But when a user searches or clicks
on the combo box again for another search if there (in the table itself is
not filled in for each row and there is a blank) is a blank in the field it
keeps the previous data. Therefore messing up the record.

I wanted to refresh to a blank screen for each new lookup from the combo box.

Should all the data be filled in on the table so this shouldn't happened or
is there another way around this?

 

[John Vinson]

On the first view everything works fine. But when a user searches or clicks
on the combo box again for another search if there (in the table itself is
not filled in for each row and there is a blank) is a blank in the field it
keeps the previous data. Therefore messing up the record.

Exactly. You have bound textboxes; in the AfterUpdate event of the
combo (that is when the user has selected a record) you are replacing
the STORED DATA in these fields with blanks. As you move to a new
record, the revised - now blank - data is stored into your table.

I wanted to refresh to a blank screen for each new lookup from the combo box.

Why?

The user has "old" data on the screen. When they select a record in
the combo box you want to jump to the selected record - right? What's
the point of blanking the screen, and then instantly - faster than the
user can see - displaying the found record?

Should all the data be filled in on the table so this shouldn't happened or
is there another way around this?

I have no trace of a notion what you mean by "filled in on the table".

Maybe I don't clearly understand what it is that you're trying to
accomplish!

John W. Vinson[MVP]

 

[Guest]

Hi John,

Hmm Let me start over.

We are taking information from a Web base database and converting it into MS
Access database. Most of the tables are partically filled (Fields have some
data in it) although not all fields are filled in therefore leaving blank
fields.

What I'm trying to do is create a form for Viewing only (next I will be
creating a new form for editing/adding/deleting). The user will pull up
infomation from the table just to look at. No input allowed here.

Maybe the problem lies with the table as some fields are blank and when the
user pulls up another record (from the combobox) and the field (ex. Address2)
that had something in it before (as in first record pulled up) but this new
record (2nd record pulled up) doesn't, it keeps the data that was in the
field before the 2nd record was pulled up. So, I thought it would be a good
idea to clear the form before looking up another record in the combo box.

Are you confused now. Or do you see what I'm trying to say.

So, my question again - Should all the data (fields) be filled in on the
table so this shouldn't happened or is there another way around this?

Thanks for your help.

Donna

 

[UpRider]

Donna, remove all code in the event procedure for its after_update. Then use
Page-UP and Page-Down to view your records.
You won't blank out any more. Your after_update code is EXPLICITLY doing
that.

UpRider

 

[Guest]

What does "Page-Up and Page-Down" got to do with my Form? The user will be
looking at a Form not the Table.

 

[UpRider]

Have you tried it? Page-up will cause the form to show the previous record,
Page-Down will show the next record, WHILE YOU ARE LOOKING AT THE FORM.

UpRider

 

[Guest]

Not on my form it doesn't. UpRider are you "John Vinson"? You don't sound
like him at all!!!

Let's back to my question. First of all I'm NOT using "Page Up or Page
Down" as it has NO meaning to my form (Combo Box) where the USERS will be
searching for the companies Name. I'm NOT Searching on the TABLE itself.


What I'm trying to do is create a form for Viewing only. The user will pull
up
infomation from the (COMBO BOX) which is linked to the table just to look
at. No input allowed here.

Does the problem lies with the table as some fields are blank and when the
user pulls up another record (from the combobox) and the field (ex.
Address2) that had something in it before (as in first record pulled up) but
this
new record (2nd record pulled up) doesn't, it keeps the data that was in the
field before the 2nd record was pulled up. So, I thought it would be a good
idea to clear the form before looking up another record in the combo box.

So, my question again - Should all the data (fields) be filled in on the
table so this shouldn't happened or is there another way around this?

 

[John Vinson]

Hi John,

Hmm Let me start over.

We are taking information from a Web base database and converting it into MS
Access database. Most of the tables are partically filled (Fields have some
data in it) although not all fields are filled in therefore leaving blank
fields.

What I'm trying to do is create a form for Viewing only (next I will be
creating a new form for editing/adding/deleting). The user will pull up
infomation from the table just to look at. No input allowed here.

How about just using a Form, with NO VBA CODE AT ALL?

This is built-in functionality in Access. You don't need to reinvent
the wheel; it's already done for you.

Simply create a Form based on your query; set its Allow Edits property
to False to prevent changing the data. As noted elsethread, PageUp and
PageDown, or mouseclicking the navigation buttons at the bottom of the
screen, will navigate from record to record for you.

If you want a Combo Box to *find* a record, based on a selected
primary key value, the Combo Wizard will build one for you, or post
back and we can help write one. But the code in your message is really
overkill (and killing your data to boot).

John W. Vinson[MVP]

 

[Guest]

How about just using a Form, with NO VBA CODE AT ALL?

I've already tried this.

Simply create a Form based on your query; set its Allow Edits property
to False to prevent changing the data. As noted elsethread, PageUp and
PageDown, or mouseclicking the navigation buttons at the bottom of the
screen, will navigate from record to record for you.

I do know that if I had the buttons PageUp and Page Down and any other
navigation will navigate the record. The USERS DON"T WANT THIS!!!!!!!!!
They want to select from the Combo Box.

If you want a Combo Box to *find* a record, based on a selected
primary key value, the Combo Wizard will build one for you.

I have the Combo Box in PLACED. With the Query.

This is NOT Answering my question at all.

DO I NEED TO HAVE THE TABLE FIELDS FILLED WITH DATA OR CAN some of the
FIELDS BE BLANK? This is my question. Why is it you think I don't have a
Combo Box in place when I do and IT'S NOT WORKING.

Frustrated!!!!

 

[UpRider]

I give up. Anybody else want to see if explaining the same thing again and
again will help? Maybe we're getting our chains jerked here.

UpRider

 

[Guest]

Well I guess since you folks can't answer my questions and you think it's
sooooo hard to understand.

Then I'll assume that ALL FIELDS IN THE TABLE NEEDS TO FILLED IN (HAVING NO
BLANKS IN TABLE) WILL RUN MY FORM CORRECTLY.

 

[John Vinson]

DO I NEED TO HAVE THE TABLE FIELDS FILLED WITH DATA OR CAN some of the
FIELDS BE BLANK? This is my question.

No, you do not need to have all table fields filled with data. The
fields may be filled or blank (unless of course it is a required
field).

The record will be displayed on the form, whether the fields within
the record contain data or not.

The absence of data in your fields is NOT the reason that your data is
being overwritten with blanks; the posted AfterUpdate event code *is*
the reason that your data is being overwritten with blanks.

I would suggest a COMPLETELY DIFFERENT after-update event for your
combo box, one which navigates the form to the desired record. Since I
do not know anything about the structure of your table, I will presume
that you have an unbound Combo Box with a bound column containing the
Primary Key of the table's recordsource, which I'll call MyID.

Private Sub comboboxname_AfterUpdate()
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[MyID] = " & Me!comboboxname
If rs.NoMatch Then
MsgBox "This ID not found"
Else
Me.Bookmark = rs.Bookmark
End If
End Sub


John W. Vinson[MVP]