In this form i have some textboxes and then a YES/NO check box.
Checkbox column in table is called "primary" and checkbox in form is called "txtPrimary"
This checkbox is supposed to allow the user to mark the adress that is "primary". The address that is the first one to use in other functions like reports etc.
But since it is a continuous form the user as it is, can select every row´s YES/NO checkbox... i just want to allow 1 checked checkbox.
Is there a way with VBA to check if there is any checkbox already set to yes, if so is, display a msgbox if not allow the user to set the checkbox to yes.
Or instead of msgbox uncheck the currently marked checkbox and set the one that the user wants.
I am convinced that there is something set improperly with your continuous form/YES/NO Check box. A Yes/No Field in a continuous form should allow for checking Yes or No for each record. This is obviously not the case, so before we get too complicated, we need to know more about your form.
Is your form a continuous form or a datasheet (some people confuse the two)?
Is the Check Box control within the Section Detail of the Form? BTW,you should also name it chkPrimary to indicate the type of control it is.
In this case, a screen shot of your form in design view might help (please trim the original image to showjust the form, as our images are limited to 500 pixels wide).
the form (adress_frm) is a continuous form, not datasheet (it should be shown in screenshots)
i know i can check yes and no for each record in a continuous form. But i only want to allow one yes for the whole form no matter if there is 100 records or just two.
if user go in to form, see 20 records (rows), then i want to control the user so that only one checkbox can be set to yes and the others stay on no...
only one adress should be able to be marked as YES and therefor be the primary adress to be used to display for example in a future report.
i know i can check yes and no for each record in a continuous form. But i only want to allow one yes for the whole form no matter if there is 100 records or just two.
if user go in to form, see 20 records (rows), then i want to control the user so that only one checkbox can be set to yes and the others stay on no...
OK - now your question makes much more sense to me!
Before we get too deep, I must ask which of the following conditions you want to apply:
The user can select one address, or another address, but never two addresses as the primary. That is, the user can freely pick and choose from any of the addresses, but never more than one
If there is already a primary address selected, the user is prohibited from choosing another.
I think you are trying to accomplish #1?
Either way, you will want to accomplish this from the BeforeUpdate event of the Check box. How you go about that could take some additional information from you.
This is not a terribly difficult solution, but this will depend on your current level of expertise in VBA. I just ask you to follow things carefully and all will be well.
To get an idea of how limited your VBA skills are:
Do you know how to create event procedures (run VBA when a certain event happens on a form, such as clicking a button or updating a text box)?
Do you know anything about creating and manipulating recordsets in VBA?
Do you understand the usage of variables in VBA?
Do you understand the basic concepts of LOOPs?
Let me know and we will work from there.
Plesae know tha tthis forum is not "for experts". However, I (and many others) want to be able to teach others more about their tools and their craft. If you are diligent and follow closely, we will be able to tackle this--although it may take a while.
Note also, that we are not in the habit of "writing your code for you". I will be doing a lot of that, by way of explanation and instruction, but I will not simply draft the solution and give it to you. If I do that, then you don't learn the concepts about proper database management and design.
If you are willing, I am also willing to go through this with you step-by-step. Hopefully, you will also learn something you can use in your future projects.
So that others, who may have a very similar question but not need as much handling as Behedwin does, can have a quick and simple answer to their question the basic approach would be :
Write an event procedure {ControlName}_AfterUpdate() that checks whether or not you now have more than one record set as Primary.
This can most efficiently be done by having a TextBox control with a formula counting where [Primary] is set and referencing that.
If so then create some UPDATE SQL that sets [Primary] to False for any other matching records.
That is to say the criteria should be set to match whatever is used in the Form itself, but with the addition of selecting only those where [Primary] is set, as well as filtering out the current record (That's just been updated to [Primary] = True).
Defensive programming suggests you should handle the possibility of multiple records being set even though it shouldn't ever happen in the normal course of events.
Do you know how to create event procedures (run VBA when a certain event happens on a form, such as clicking a button or updating a text box)?
Yes, i think know this.
Do you know anything about creating and manipulating recordsets in VBA?
no, this dont know much about....
Do you understand the usage of variables in VBA?
Yes the basics
Do you understand the basic concepts of LOOPs?
No, loops i have used but i dont really understand them.
Im not expecting to get a finished code done and handed to me. But since i dont know where to start and how to even approach this problem... I might need some hints.
Looking around i found simular questions giving talking about using Dcount to solve this....
Expand|Select|Wrap|Line Numbers
If Dcount("yesnofield","yourtable',"yesnofield = true ") > 0 then
or
Expand|Select|Wrap|Line Numbers
Dim strsql
If Me.txtPrimary = True Then
strsql = "Update adress_tbl set txtPrimary = False where ID <> " & Me.Adress_ID
CurrentDb.Execute strsql, dbFailOnError
Me.Refresh
Else
' do nothing or tell user they must select a new value
End If
This code i have tested but i get an error on the "currentdb.execute" line....
Error says "to few parameters, expected 2"
Great seems like those three points cover what im after.
Ill take my chances and speculate a bit on how it theoretical could be solved....
If there is no Priamry already selected, that record become the Primary
- In the "click event" of the checkbox: A loop that looks in the table column and see what records are set to YES, that record is saved in a variable and then told to be set to NO. Then the record the user clicked on would be set to YES and the rest set to NO (those already at NO would not be effected).
If there is a Primary already selected, we want to clear it and set the current record as Primary
- from the loop i now know what record is set to YES... so with that information i can tell access to set that record to NO....
We also have to allow for “unchecking” the Primary
- unchecking the primary should just allow the checkbox to be set NO since the loop wont find any YES records and therefor cant set any records to NO since there are no YES.... and the click would just work as a normal click and set the clicked checkbox to NO.
Tried to google for some loop codes, i find plenty. but the lack of understanding the code i dont know really what it is doing.
I'm going to ask two things up front: Change the name of teh Check box you are using on your subform to chkPrimary (so that we can all be on the same naming convention) and make sure that your DB is set to declare variables explicitly: In your VBA editor, to to Tools, Options, and make sure that the Require Variable Declarations box is checked. This will ensure that at the top of all of your new modules, you will find the following statements:
Expand|Select|Wrap|Line Numbers
Option Compare Database
Option Explicit
Why this is not the Microsoft Deafult is far beyond anyone's comprehension.
First, I want to say that "conceptually" you have a good (great) idea of what needs to happen. Let's play around a bit and see if we can learn a few things about how Access updates tables first.
When using a Check Box, whether you use the OnClick, BeforeUpdate or AfterUpdate Events, when you check the value of the Check Box (or the Field) in VBA it will always be the value of the Check Box after you have clicked it. You can check this by using the Debug Command:
Expand|Select|Wrap|Line Numbers
Debug.Print "Table: " & Me.Primary
Debug.Print "Form: " & Me.chkPrimary
Notice how I am displaying the value that Access finds in the Table (Me.Primary) and the value on the Form (Me.chkPrimary). If we were to apply this code to your Form, every time you clicked the Check Box, if it was already checked and you unchecked it, your immediate Window would show:
Expand|Select|Wrap|Line Numbers
Table: 0
Form: 0
If it was unchecked and you checked it:
Expand|Select|Wrap|Line Numbers
Table: -1
Form: -1
This is important for you to know how the DB is going to look at your data. But also, this tells us something else. It doesn't matter which event we use, because the system will always find the same values.
For our example, let's just stick with the AfterUpdate Event, as we know we can work with this very easily.
Some important aspects of this exercise will be important. You have a form that displays "People", I suppose. And on the Form there is a sub-form in Continuous mode that lists each address associated with each Person, correct? What is important is that there is a "PersonID" or some primary key related to the People table that is also in your Addresses table.
If you would be so pleasant as to provide your Tables Names (for People and for Addresses), as well as the significant Field Names (the People Primary Key, the "Primary" field name in the Addresses table) and we can start to play with this in a more meaningful way.
May I suggest you study my short post (#7) and consider the approach. It's not how everyone would do it because not everything is as obvious as it might be. I'm happy to answer questions as to why I've suggested that way if you find anything that isn't as you'd expect.
I believe that is the general direction I am going. Although I do have one question about Post #7.
Conceptually, if, after the user sets the Primary Address (or clears it), we set all other addresses to non-Primary, so that only one address can be primary, why would we also include a text box listing how many primary addresses we have. I can, however, foresee a user (or admin) looking through records of people to find out how many have multiple Primary addresses--but I would approach that from a DB management perspective and have that function non-associated with this particular form.
Twinny:
why would we also include a text box listing how many primary addresses we have
That would be because we don't want to be nooby and use a domain aggregate function like DCount() when we can do it within the existing recordset of the form.
Fundamentally, recordsets are (relatively*) expensive on both resources and time. They're also important to get exactly right. You may have a straightforward form, but what if it were created from a number of tables and either the basic recordset or its current state of filtering involved a lot of complicated SQL? A control on the form itself benefits from showing you exactly what you have, and with almost no effort. Reproducing that using DCount() can be complicated and hard to make up reliably. And it's just nooby code. You shouldn't ever be looking to use them where you can do it a more natural way.
Massively powerful and useful in many situations, but shouldn't be used as an excuse not to use what you already have available.
* Computers have so many resources that a little use here and there will not even be noticeable. Nevertheless, over time, and with re-used procedures, it can add up.
What purpose does the text box with the number of Primary addresses for each person serve? Again, if we clear all other Primary values when we check another as primary, whether we have one, none or a dozen, I don't see a purpose, other than oversight for the user (which is not necessarily meaningless).
I do think a better approach is to use an update query, as you described. Our earlier posts were on top of each other and I did not take that into acocunt with my original thoughts.
The colution for this should be fairly simple, but I do want to guide through a solution, rather than just giving it, which I think you would agree is a more meaningful approach.
Twinny:
Again, if we clear all other Primary values when we check another as primary, whether we have one, none or a dozen, I don't see a purpose, other than oversight for the user (which is not necessarily meaningless).
I suspect you haven't quite followed the logic from post #7.
The TextBox is an easy way to determine if there are any records in the current recordset that need to be cleared. We don't want to run an UPDATE query if there's nothing to do. We particularly don't want to have to create that complicated query if it's unnecessary (even though we both know the code to create it needs to be there whether we run it on any particular occasion or not). We run that (as per point #2) if, and only if, we determine there is anything to do.
It may be that the same information could be found without the TextBox by simply processing the Recordset of the Form. I haven't checked into that as I knew the TextBox approach works and is pretty simple. That could be looked into if you dislike the TextBox approach.
I hope that answers your question. If not then I've not understood it correctly so please explain further. I certainly don't want to leave any confusion.
NO, that explains it perfectly. I was overlooking the fact that if there were none already, then do nothing. I guess I am thinking "small scale" and if we clear everything again--no big deal. But, from a design perspective, only do something if you have to. Makes perfect sense now.
Hey
Im sorry for not responding, my vacation kicked in and i have had a bit less time infront of the computer.
1. I have changed the name of the checkbox to chkPrimary.
2. I already have this sorted.
Expand|Select|Wrap|Line Numbers
Option Compare Database
Option Explicit
3. I get an error when running this line
Expand|Select|Wrap|Line Numbers
Debug.Print "Table: " & Me.Primary
Error says: method or data member not found
4. Yes i have a table for people.
Tablename: Profile_tbl
It contains the ID column: Profile_ID
In Adress_tbl i have the Profile ID that is called: Profile_ID_SK
Adress table is called: Adress_tbl
Adress table contain ID named: Adress_ID
So, we're gonna go a bit slow so we can make sure each piece works before me move on to the next.
First, on your Address_Frm, place a text box called txtCountPrimary (create a header for your form if you don't have one, and place it there). Its record Source will be =Sum(IIf(Primary,1,0)).
This will be the first step. Open your form and as you cycle through people, this text box should display the number of addresses that are listed as "Primary". If all of your records are set to "Not" Primary, just go into the Table directly and set some to primary (and multiples for some people--we just want to make sure this aspect works first.
Once we get this working, we will move on to the next step.
One could make the formula in the textbox simpler: =Sum([Primary])
This will return a negative number as Access returns [True=-1]
If you really want a positive number: =-1*(Sum([Primary]))
From there it should be easy enough to capture the current record's [Adress_ID] and the [Profile_ID_SK] and execute an update query such that
all records where [Profile_ID_SK]=<captured Profile ID>
AND all record where [Primary]=true
AND [Adress_ID]<> <captured Adress ID>
Set [Primary]=false
So a generic version might look like:
Expand|Select|Wrap|Line Numbers
UPDATE Table3 SET Table3.Field2 = False
WHERE ((Not (Table3.ID)=14) 'this would be [Adress_ID] of the just set record
AND ((Table3.Field1)=1) 'this would be [Profile_ID_SK] in the just set record
AND ((Table3.Field2)=True)); 'just effect records with [Primary]=true in the Adress_Tbl for the [Profile_ID_SK]
One could build the SQL on the run in VBA or use the [TempVars] to pass the information to a stored query. The on the run method may have the advantage of using the Database.execute method which bypasses the warning nag and provides a bit of error trapping and, if you want to get fancy, transaction to rollback if an error occurs.
Twinnyfo,
Since you brought it up in the linked article... good thought and as usual NeoPa has some very good advice; however, I haven't used a Boolean field in my tables for a very long time because of this article: Allen Browne: Why I stopped using Yes/No fields
(which avoids another issue with Boolean fields nulls, access front ends and SQL backends) :)
I can't really argue with that (or Allen Browne ever pretty well) Z, but I do think it's a shame. Most Access designers haven't had a good grounding in computer principles and struggle to work well with booleans, whether they're stored in a Boolean field/variable or otherwise. I believe the understanding of logical booleans is essential for good solid programming. Sure, those that have that grounding can still use integer variables to store them, but that would condemn most Access designers to avoid ever having to deal with, and thus get a chance to learn about, the basics of boolean logic. One of the most fundamental concepts that underpins logic, which itself is a fundamental concept for any software.
So, where I'm happy to see that Allen has highlighted this issue, I'm far less happy that fewer people will be exposed to having to think about and understand booleans because of it.
Are you compiling and this is producing an error? Or are you executing the code and it is producing an error during execution?
If it is during execution, add a line betwixt lines 11 and 12:
Expand|Select|Wrap|Line Numbers
Debug.Print strSQL
I am baffled with the error, as this executed perfectly on my form.
The only thing I can think is that the table or Pofile_ID_SK are misspelled.
HOWEVER——— I have experienced this strange phenomenon in the past. Do this: Go to the field chooser for your form. Drag the Profile_ID_SK field onto your form—make sure that the control for it is named: Profile_ID_SK. Save the form. Now, DELETE that control. All should work...... Oddly enough.
I have been gone for some time. Hopefully this havent become to old.
@twinnyfo
I did as you said.
Expand|Select|Wrap|Line Numbers
HOWEVER——— I have experienced this strange phenomenon in the past. Do this: Go to the field chooser for your form. Drag the Profile_ID_SK field onto your form—make sure that the control for it is named: Profile_ID_SK. Save the form. Now, DELETE that control. All should work...... Oddly enough.
This changed the error tho.
Now i get this error:
Line 13
See screenshot 1
I get this error when running the code.
Ie. i try to mark a checkbox.
I also get the same error when pressing Debug > compile
Dont know why this is happening. Im a bit confused since you say you get it to work... and access are not telling me anything really about the error.
EDIT
I found a solution
Changed this:
Expand|Select|Wrap|Line Numbers
With DoCmd
.Set Warnings False
.Run SQL strSQL
.Set Warnings True
End With
to this
Expand|Select|Wrap|Line Numbers
With DoCmd
.SetWarnings False
.RunSQL strSQL
.SetWarnings True
End With
and now it works.
so far i think i got everything working as intended.
Perhaps one of the more smarter people on this forum can explain this...
So, when you add a Record Source to a Form, you can refer to the values of the underlying Record Source directly by using the following:
Expand|Select|Wrap|Line Numbers
Me.FieldName
This is one of the reasons that when you add fields to a Form, always, always, always, ALWAYS, ALWAYS rename the control using a proper naming convention (e.g. txtFieldName). If you don't, when you refer to the Text Box you just created (which has the name FieldName) by simply using:
Expand|Select|Wrap|Line Numbers
Me.FieldName
You are actually being ambiguous, because Access doesn't know for sure whether you are referring to the Field or to the Text Box. I believe it defaults to the underlying Field. So, if you have made changes to the value in a Text Box and the value hasn't updated yet, and try to use that value by referring to it with the method above, you may get unexpected results.
Sometimes you want to refer directly to the Record Source--such as on a Form when there is no Text Box for that particular Field. For example, you don't want (or need) to have a text box displaying the underlying Primary Key, but you often refer to the Primary Key. So, using:
Expand|Select|Wrap|Line Numbers
Me.Profile_ID
makes a lot of sense.
However, for whatever reason (and I haven't found any consistency to this phenomenon), sometimes your VBA thinks that the Field Profile_ID doesn't exist. Now, when you add that field directly to the Form (drag and drop from Field chooser) and then immeidately delete the control created, for whatever reason, the VBA now recognizes that field and all is swell!
If someone can explain why this happens, I'd really appreciate it. But, I've encountered this phenomenon countless times, and I have simply learned to just add the control and delete it.
TwinnyFo:
Perhaps one of the more smarter people on this forum can explain this...
Never a big fan of tautology, but I'll give what help I can.
I can only really say that the ambiguity issue has been explained to the Access Team in the past. It's an unfortunate default to say the least.
Why you can sometimes refer to a Field that way and can't at others is not something I understand. I'd need to see the actual situation. One possibility however, is that VBA and the Expression Service, that handles the formulas etc, are quite different and often follow different rules. Is it possible that VBA can see the Field whereas the ES cannot? The ES doesn't handle dots (.) if I remember correctly, so would need the bang (!). The bang would cause it to refer to a Control rather than a Field so this may explain the confusion.
I'm certainly behind your call to ensure all Controls are named consistently and to indicate the Control type in the name.
I am using the following code (created by the wizard) to
allow to bring my form to a particular entery. But when I
edit the entery (ex: put new information into a blank
cell), it puts that record...
I have a form that consists of 150 records and is still growing.
there are times when I want to print a certain record of the form, say
record 12. I go to file --> print and choose the page number...
There is a form (single form) and a combobox. I want that current record of
the form is adjusted according to selected value in the combobox. Cuurrent
record should be the same as the value in the...
This is kind of an odd request.
I have a form with a combo box. If an item isn't in the combo box you can add it by launching a new form (using NotInList). I would like for the main form to go...
Just a quick question?
I want to delete the record from my form. I have a command button in the footer area. When ever I click on the delete button for that specific record, my form goes to the...
I am trying to save the current record on a form before opening a
report, doesn’t sound to hard does it? The code on a buttons on click
event goes like this:
DoCmd.DoMenuItem acFormBar,...
Subject: Trying to open up a record in a form called "fmappointment" when clicking on a "looks like hyperlink" text box called "ContactID"
I have made many instances of this in my database but in...
i would like to add a new record to a form and have it appear after the current record i am on... so for instance if i am on record 2 of 5 i would like to insert a new record that would now be the...
In Access 2007, I am trying to add a button to my sata entry split form that will delete the current record, after prompting, but I am running into a bizarre issue.
Here is the relevant code....
I have a form in which users can create new records or modify existing records. All records are stored in one table. I want to be able to keep track of all changes made to existing records as well...
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
17
