clear a field if a second field has data

[megatronixs]

Hi all,

I have a form with a subform. On the subform I have a field that has customer number in it, on the main form there is a field that will need to say "none" if the field with the customer number is empty, and empty if the customer number field is filled in. the field will not print out in the letter if there is no data on it.

the customer number comes from the table "CustomerNumbersData"
the the field that needs to be empty on the main folder comes from table "CustomerData"

Hope some one can help out.

Greetings.

 

[vbaInet]

Your explanation isn't clear. Show us some data and tell us what text goes on which record.

 

[jdraw]

I agree with vbaInet, also what are the link fields between form and subform.

 

[megatronixs]

Hi,
when I open a new record on the database the field "customer number (main form) says standard "None"
When I fill in the subform the first customer number, the field customer number in the main form should be cleared as on the letter it will appear the numbers and need to avoid the word "None" below.

Is this better?

Greetings.

 

[megatronixs]

the link is based on the IDnr on both tables.

 

[vbaInet]

Hi,
when I open a new record on the database the field "customer number (main form) says standard "None"
When I fill in the subform the first customer number, the field customer number in the main form should be cleared as on the letter it will appear the numbers and need to avoid the word "None" below.

What's the purpose of that field? It seems to be a redundant field.

 

[megatronixs]

hi,
we send out a letter to the clients with all their contact numbers on it, if they don't have one yet or at all, then there is a text that says "none". If they have contact numbers, then they appear one below the other. That field needs to be there just in case no numbers are.

greetings.

 

[Galaxiom]

The query used to create the letters should include a join to the table or query used as the subform's RecordSource and the "None" value derived from the absence of related records.

 

[megatronixs]

HI Galaxiom,

It sounds so easy, but I don't use a query for the letter creation, it is just a report that takes the info from the fields. I was thinking about an IF statement, but my access knowledge is not the one from excel (I still try to figure out how to do vba in access)

Greetings.

 

[Galaxiom]

The report has a RecordSource Property. That can be a query instead of a table.

Without knowing the structure of your data it is hard to say what that query would look like.

the customer number comes from the table "CustomerNumbersData"
the the field that needs to be empty on the main folder comes from table "CustomerData"

That sounds unusual. Please tell us what fields are in these tables and how they relate to each other

 

[megatronixs]

Hi,

The table "CustomerNumbersData" has 3 fields. IDnr, CustomerNumber, ContactPerson.
The Table "CustomerData" has also the IDnr (is the Primary Key) and the field that the text "None" is as starting text inside. I combine this in the report with the fields to collapse if there is no data. But as the text "None" is there, the field does not collapse and shows under the customer numbers in the letter. I was thinking that If I would use an IF statement and telling the field that shows the "None" text to look first in the field CustomberNumber from the table CustomerNumberData, if there is text in it (not empty) then stay empty, if no text in it, show the text "None".

I used something similar in a field to uncheck a box:

Private Sub Command51_Click()
Me.[DateAuthRec] = Date 'set the date received auth,
Me.Refresh

If IsDate(DateAuthRec) Then   'when the receive date is filled it will uncheck the checkbox
  NOAUTH = False
Else
  NOAUTH = True
End If

If IsDate(DateAuthRec) Then   'when the receive date is filled it will uncheck the checkbox
  SentToAud = True
Else
  SentToAud = False
End If

End Sub

I had some help with the code and I don't understand it for 100%.

Greetings.

 

[Galaxiom]

You are going down the wrong path. Instead of the checkbox you should have been testing for a Nulls in the date fields.

You need to read up on database normalization. This is one of the most important principles in databases. One if its premises is that conflicing data cannot be held. By storing both the date fields and checkboxes you are breaking this principle.

With regards to your current question, I am wondering why you have two separate tables.

 

[megatronixs]

Hi the 2 separate tables are needed as there could be on customer number or many of them up to 170 and more. If I would put them on one table, I need to add a lot of fields to it. This way I have one table with all of them and linked to the customer so I can keep all numbers linked to on customer.

The checkbox is so there is a second version of the letter depending on if it is selected or not.

As I'm still new to databases, I understand that I have a lot to learn

 

[vbaInet]

What I understand from your post #11, CustomerData stores details of the customer whilst CustomerNumbersData stores the customer's phone number(s). That's fine.

I don't know what Command51 actually represents but you should not be storing values in NOAUTH and SentToAud. You don't need those two fields, they are redundant just like your "None" field.

All of this is calculated and you simply calculate it in a textbox or in a query, no need to save it.

So for the "None" part you can count how many records exist in the subform, if it returns 0 or errors then set the value of the textbox (not the field but the textbox) to "None".

To re-iterate, you don't need the fields None, NOAUTH and SentToAud. Calculate them all.