Condition in an Access form
Solved
faon
Posted messages
16
Status
Membre
-
jluc -
jluc -
Good evening,
I am new to this site and I have a question to ask
I am in the process of creating a database and I have a problem regarding a field
I explain:
I have three fields: Assignment, Bank account number and Bank account number (R)
If I have a bank account number that appears in either of the two "account" fields, the Assignment field must remain empty.
If I have no account in either of the two "account" fields, it should return the text "Assignment" in the Assignment field.
I have been searching for days and I can't find the solution.
Who can help me? Thank you in advance
I am new to this site and I have a question to ask
I am in the process of creating a database and I have a problem regarding a field
I explain:
I have three fields: Assignment, Bank account number and Bank account number (R)
If I have a bank account number that appears in either of the two "account" fields, the Assignment field must remain empty.
If I have no account in either of the two "account" fields, it should return the text "Assignment" in the Assignment field.
I have been searching for days and I can't find the solution.
Who can help me? Thank you in advance
Configuration: OFFICE 2000 WINDOWS 97
9 réponses
Well, it doesn’t matter in my opinion: it’s a question of presentation.
On the other hand, 100 fields for a table is a lot...
I don’t know your data type at all. Are you sure you’re not confusing fields and records (sorry, don’t take my question the wrong way... ).
Why do you want to export the results to Word?
Reports are not bad in Access. They don’t require manipulation once they’ve been designed and they allow you to apply filters or groupings more easily than form fields in Word.
On the other hand, 100 fields for a table is a lot...
I don’t know your data type at all. Are you sure you’re not confusing fields and records (sorry, don’t take my question the wrong way... ).
Why do you want to export the results to Word?
Reports are not bad in Access. They don’t require manipulation once they’ve been designed and they allow you to apply filters or groupings more easily than form fields in Word.
Hello,
Suggestion:
Switch the 2 "After Change" events to the same function.
Sub [Bank Account Number]_AfterChange
Validation
End Sub
Sub [Bank Account Number(R)]_AfterChange
Validation
End Sub
Function Validation
If (([Bank Account Number] <> "") Or ([Bank Account Number(R)]) <> "") Then
[Assignment].Value = ""
Else
[Assignment].Value = "Assignment"
End IF
End Function
Does this meet your expectations?
Lupin
Lupin
Suggestion:
Switch the 2 "After Change" events to the same function.
Sub [Bank Account Number]_AfterChange
Validation
End Sub
Sub [Bank Account Number(R)]_AfterChange
Validation
End Sub
Function Validation
If (([Bank Account Number] <> "") Or ([Bank Account Number(R)]) <> "") Then
[Assignment].Value = ""
Else
[Assignment].Value = "Assignment"
End IF
End Function
Does this meet your expectations?
Lupin
Lupin
Hi Lupin,
since I've noticed that you master VBA, could you tell me if what I'm trying to do is feasible?
Here's the link http://tinyurl.com/mk9f6
Thank you in advance,
lami20j
since I've noticed that you master VBA, could you tell me if what I'm trying to do is feasible?
Here's the link http://tinyurl.com/mk9f6
Thank you in advance,
lami20j
Hi,
Lupin doesn't seem to be here...
In my opinion, you need to add the reference to the proposed function in the field properties in a form...
(But from your post, I'm not sure if you're at that point.)
If that's the case, in design mode, right-click on the field in question, choose properties, then under the event tab, AfterUpdate, select "event procedure," and you'll find yourself in the VB window where you type the proposed code.
Then, you need to write the module in which the proposed function should be copied.
If you're in a table, that's not possible... but you can write an update query that will "correct" your data all at once.
I hope this helps you
(sorry, Lupin, for "treading on your toes"...
Lupin doesn't seem to be here...
In my opinion, you need to add the reference to the proposed function in the field properties in a form...
(But from your post, I'm not sure if you're at that point.)
If that's the case, in design mode, right-click on the field in question, choose properties, then under the event tab, AfterUpdate, select "event procedure," and you'll find yourself in the VB window where you type the proposed code.
Then, you need to write the module in which the proposed function should be copied.
If you're in a table, that's not possible... but you can write an update query that will "correct" your data all at once.
I hope this helps you
(sorry, Lupin, for "treading on your toes"...
Hello everyone,
for zenon, no mistakes, we are here to share,
and I certainly don't claim to know or
understand everything. On the contrary, thank you for following up.
I give the help I can with the time I have. I don’t always
have it. Perhaps other ccmmists don’t like this kind of intervention, but for my part, the goal
is always to help the person who is asking and without a doubt [ united we stand ].
That said, I saw things exactly as you described.
Of course, I don’t know faon’s skills in VBA.
For now, I can't go any further because I have to get
to work. If these answers are not clear, I will continue
to follow the post to help you faon.
P.S. lami20j, I'll take a look at your thread!
@+
Lupin
for zenon, no mistakes, we are here to share,
and I certainly don't claim to know or
understand everything. On the contrary, thank you for following up.
I give the help I can with the time I have. I don’t always
have it. Perhaps other ccmmists don’t like this kind of intervention, but for my part, the goal
is always to help the person who is asking and without a doubt [ united we stand ].
That said, I saw things exactly as you described.
Of course, I don’t know faon’s skills in VBA.
For now, I can't go any further because I have to get
to work. If these answers are not clear, I will continue
to follow the post to help you faon.
P.S. lami20j, I'll take a look at your thread!
@+
Lupin
Thank you for your email, and it’s very kind of you, especially since those I had approached before signing up for this site were the type to say, "I know, but figure it out on your own."
In terms of Access, I attended the class, but my training was based on the essentials (tables, forms, queries, etc.) and as for VBA, I am discovering it myself and gradually learning through the applications I’m trying to create.
And since I really like Access, I want to go further.
Thanks again.
In terms of Access, I attended the class, but my training was based on the essentials (tables, forms, queries, etc.) and as for VBA, I am discovering it myself and gradually learning through the applications I’m trying to create.
And since I really like Access, I want to go further.
Thanks again.
Hello,
To keep it simple,
First Floor
A TableA
A RequeteA that encompasses Table A
A Form that points to the request
Second Floor
Several simple requests pointing to a record
Some multiple-choice controls on one, two, or three sub-forms
Some lines of VBA
As explained by Zenon, in design mode there is a window that contains the properties of the object, Events tab, you should find the method "After Update", in French, it would be "Après MAJ".
You should land in the code of the routines I referred to in my example.
Lupin
To keep it simple,
First Floor
A TableA
A RequeteA that encompasses Table A
A Form that points to the request
Second Floor
Several simple requests pointing to a record
Some multiple-choice controls on one, two, or three sub-forms
Some lines of VBA
As explained by Zenon, in design mode there is a window that contains the properties of the object, Events tab, you should find the method "After Update", in French, it would be "Après MAJ".
You should land in the code of the routines I referred to in my example.
Lupin
Hello and a thousand apologies for dropping it for a few days, feeling discouraged.
All I have left is this damn "Account Number" field, "Account Number (R)," and "Assignment" from my form to finish my application, and I'm floundering.
In the first two, I took the macro from the website www.fundp.ac.be/jmlambert/vba/Notes1-Exemples-2.html and worked it into the "After Update" event procedure. Although I'm entering a correct account number, it keeps returning an incorrect bank account.
As a result, I no longer know how to write an event procedure or function for my assignment field.
I'm terrible at VBA; any advice would be greatly appreciated.
Thanks in advance.
All I have left is this damn "Account Number" field, "Account Number (R)," and "Assignment" from my form to finish my application, and I'm floundering.
In the first two, I took the macro from the website www.fundp.ac.be/jmlambert/vba/Notes1-Exemples-2.html and worked it into the "After Update" event procedure. Although I'm entering a correct account number, it keeps returning an incorrect bank account.
As a result, I no longer know how to write an event procedure or function for my assignment field.
I'm terrible at VBA; any advice would be greatly appreciated.
Thanks in advance.
Good evening,
I do not understand where the problem lies.
With independent text fields, the technique proposed by Lupin works perfectly... (it is not necessary to write a module)
Have you tested it in an independent form?
Perhaps the problem lies in the update of the "assignment" field. If you display the result in the detail section of a form and it is an independent text field, all records will take the same value when you execute the code in one of them since this independent control does not refer to data from the underlying table. Is that the problem?
To avoid this type of issue, the simplest would be to add an "assignment" field to the table and execute an update query for all existing records. I can explain how to do that if you're interested...
Then, when adding or updating records, the code proposed by Lupin will take over without any issues.
If you do not want to "pollute" your table with redundant data, you can base the form on a query and temporarily create the assignment field, then execute a new query to update it.
You can also loop through all the records to set the value by reusing part of Lupin's code... and creating the control temporarily.
In short, there are plenty of solutions...
What do you think? It is difficult to help you without knowing your data structure.
I do not understand where the problem lies.
With independent text fields, the technique proposed by Lupin works perfectly... (it is not necessary to write a module)
Have you tested it in an independent form?
Perhaps the problem lies in the update of the "assignment" field. If you display the result in the detail section of a form and it is an independent text field, all records will take the same value when you execute the code in one of them since this independent control does not refer to data from the underlying table. Is that the problem?
To avoid this type of issue, the simplest would be to add an "assignment" field to the table and execute an update query for all existing records. I can explain how to do that if you're interested...
Then, when adding or updating records, the code proposed by Lupin will take over without any issues.
If you do not want to "pollute" your table with redundant data, you can base the form on a query and temporarily create the assignment field, then execute a new query to update it.
You can also loop through all the records to set the value by reusing part of Lupin's code... and creating the control temporarily.
In short, there are plenty of solutions...
What do you think? It is difficult to help you without knowing your data structure.
Good evening Zenon,
I found what I need in Excel (End of Month) it works in Excel but I can't activate the function in Access which returns #Name?. Utility activated. What to do? Thank you for your response. If possible, I would like to create it as a function?
I found what I need in Excel (End of Month) it works in Excel but I can't activate the function in Access which returns #Name?. Utility activated. What to do? Thank you for your response. If possible, I would like to create it as a function?
Strange, it's not working for me either, even after loading the DLL ""o???"" mentioned in the help... and it doesn't work in Excel either... where is Bill??
I wrote a little function:
Private Sub ...
Dim X As Date
For X = Date To X = Date - 31 Step -1
If Month(X) = Month(Date) - 1 Then
ChampIndépendantADéfinir = X
Exit For
End If
Next X
End Sub
It works for me.
I wrote a little function:
Private Sub ...
Dim X As Date
For X = Date To X = Date - 31 Step -1
If Month(X) = Month(Date) - 1 Then
ChampIndépendantADéfinir = X
Exit For
End If
Next X
End Sub
It works for me.
Hello everyone,
In my form, I have a field where I need to enter a code, for example 1000, and when this code is entered, I would like a corresponding term to automatically appear in another field.
I have a table with the two fields in question. There is one column with the code and another with the corresponding terms, for example 1000 corresponds to housing, 8180 to chalet, etc.
I don't know anything about Visual Basic...
Who can help me?
Thank you very much in advance.
In my form, I have a field where I need to enter a code, for example 1000, and when this code is entered, I would like a corresponding term to automatically appear in another field.
I have a table with the two fields in question. There is one column with the code and another with the corresponding terms, for example 1000 corresponds to housing, 8180 to chalet, etc.
I don't know anything about Visual Basic...
Who can help me?
Thank you very much in advance.
Hello,
Writing a filter in VBA for opening a form is fine.
However, I do not know how to make it keep that filter throughout the time I am in the form and while I am making other filters.
Let me explain with an example:
I open a form f_Client sorted by the client's city of origin.
In this form, I want to make "sub-sorts" via drop-down lists (age, name...)
So far, so good, the sorting works well.
However, if I decide to clear my sub-sorts, it clears everything and shows me all the clients from all cities and no longer just the one that was selected at first.
How can I ensure that an initial filter remains unchanged as long as I am in the form?
Thank you in advance for your responses.
Seb
Writing a filter in VBA for opening a form is fine.
However, I do not know how to make it keep that filter throughout the time I am in the form and while I am making other filters.
Let me explain with an example:
I open a form f_Client sorted by the client's city of origin.
In this form, I want to make "sub-sorts" via drop-down lists (age, name...)
So far, so good, the sorting works well.
However, if I decide to clear my sub-sorts, it clears everything and shows me all the clients from all cities and no longer just the one that was selected at first.
How can I ensure that an initial filter remains unchanged as long as I am in the form?
Thank you in advance for your responses.
Seb
No, don't worry I don't take it badly at all, especially coming from someone who knows much more than I do on the subject.
Yes, I really have 100 fields on a single table. I should say I had because it was too cumbersome, I started everything over again (practice makes perfect).
My project is based on seven tables and a form based on queries.
The people I supervise receive disabled individuals and must fill out a complete form. This form serves as a starting point to complete a file full of documents, which is why I wanted to merge it with a Word text file. You are certainly right, I haven't tried to do it with a report.
Is there a function that calculates the first day of the previous month?
Thank you for your kindness in guiding me step by step.
invoice date 10/05/2004; old vehicle date 25/08/2002
hence revision from 01/08/2002 to 31/07/2005
withdrawal 10/05/2004 (= invoice date)
period to review 01/06/2004 to 31/07/2005, which is 14 months
the problem lies with the date of 01/08/2004, all the other formulas I have found.
All dates are fields.
Hope I was able to explain myself properly.