423,309 Members | 2,163 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,309 IT Pros & Developers. It's quick & easy.

Set Record as Primary on Continuous Form

P: 17
Hey

I have a table called
Adress_tbl

Then i have a Continuous form called
Adress_frm

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.
Jul 3 '18 #1
Share this Question
Share on Google+
34 Replies


twinnyfo
Expert Mod 2.5K+
P: 2,532
behedwin,

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).

I hope this hepps! Standing by for more!
Jul 3 '18 #2

P: 17
okey here are some screenshots.

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.

fyi: the textbox called adress_id is hidden.





Attached Images
File Type: jpg Screenshot_1.jpg (24.6 KB, 243 views)
File Type: jpg Screenshot_2.jpg (42.8 KB, 245 views)
File Type: jpg Screenshot_3.jpg (36.9 KB, 243 views)
File Type: jpg Screenshot_4.jpg (62.1 KB, 243 views)
File Type: jpg Screenshot_5.jpg (31.1 KB, 239 views)
Jul 3 '18 #3

twinnyfo
Expert Mod 2.5K+
P: 2,532
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:
  1. 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
  2. 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.

Standing by for more hepp!
Jul 3 '18 #4

P: 17
Yes, option 1

My lvl of VBA knowledge is somewhat limited.
Jul 3 '18 #5

twinnyfo
Expert Mod 2.5K+
P: 2,532
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.
Jul 3 '18 #6

NeoPa
Expert Mod 15k+
P: 30,996
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 :
  1. 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.
  2. 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.
Jul 3 '18 #7

P: 17
Okey ill try to give you more info.
  • 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
  1.  If Dcount("yesnofield","yourtable',"yesnofield = true ") > 0 then
or

Expand|Select|Wrap|Line Numbers
  1. Dim strsql
  2.  
  3. If Me.txtPrimary = True Then
  4. strsql = "Update adress_tbl set txtPrimary = False where ID <> " & Me.Adress_ID
  5. CurrentDb.Execute strsql, dbFailOnError
  6. Me.Refresh
  7. Else
  8. ' do nothing   or tell user they must select a new value
  9. End If
This code i have tested but i get an error on the "currentdb.execute" line....
Error says "to few parameters, expected 2"
Jul 5 '18 #8

twinnyfo
Expert Mod 2.5K+
P: 2,532
Well, it looks like you are willing to learn, and that is all we ask.

I will come back to this a bit later this morning and we will begin working through this.

For now think about this conceptually. What do we want to happen when someone clicks the Primary check box?
  • If there is no Priamry already selected, that record become the Primary
  • If there is a Primary already selected, we want to clear it and set the current record as Primary
  • We also have to allow for “unchecking” the Primary

Just think about ways we might do this. I’ll come back later and build a skeleton for you that we can flesh out in greater detail.

This will be a great learning process for you!
Jul 5 '18 #9

P: 17
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.
Jul 5 '18 #10

twinnyfo
Expert Mod 2.5K+
P: 2,532
OK - I'm back and able to begin this process.

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
  1. Option Compare Database
  2. 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
  1. Debug.Print "Table: " & Me.Primary
  2. 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
  1. Table: 0
  2. Form: 0
If it was unchecked and you checked it:
Expand|Select|Wrap|Line Numbers
  1. Table: -1
  2. 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.

Standing by to move forward!
Jul 5 '18 #11

NeoPa
Expert Mod 15k+
P: 30,996
Hi Twinny.

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.

{Prepared before post #11 but still relevant.}
Jul 5 '18 #12

twinnyfo
Expert Mod 2.5K+
P: 2,532
@NeoPa,

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.

Perhaps I am missing something obvious?
Jul 5 '18 #13

NeoPa
Expert Mod 15k+
P: 30,996
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.
Jul 5 '18 #14

twinnyfo
Expert Mod 2.5K+
P: 2,532
Perhaps my question was unclear.

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.
Jul 5 '18 #15

NeoPa
Expert Mod 15k+
P: 30,996
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.
Jul 5 '18 #16

twinnyfo
Expert Mod 2.5K+
P: 2,532
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.
Jul 5 '18 #17

NeoPa
Expert Mod 15k+
P: 30,996
Great. That's one of the points I was trying to get across in post #7.

It's easy to do something that will work. It's not always obvious how to do it well.

Trying to be perfect obsessively can ruin an otherwise productive life. Trying to be the best you can be as a general rule though, works for me.
Jul 5 '18 #18

P: 17
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
  1.  
  2.     Option Compare Database
  3.     Option Explicit
  4.  
  5.  
3. I get an error when running this line
Expand|Select|Wrap|Line Numbers
  1.     Debug.Print "Table: " & Me.Primary
  2.  
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


see picture for full table info.


Attached Images
File Type: jpg 1.jpg (53.1 KB, 142 views)
File Type: png 2.png (13.3 KB, 140 views)
Jul 11 '18 #19

twinnyfo
Expert Mod 2.5K+
P: 2,532
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.
Jul 11 '18 #20

zmbd
Expert Mod 5K+
P: 5,279
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
  1. UPDATE Table3 SET Table3.Field2 = False
  2. WHERE ((Not (Table3.ID)=14) 'this would be [Adress_ID] of the just set record
  3.   AND ((Table3.Field1)=1) 'this would be [Profile_ID_SK] in the just set record
  4.   AND ((Table3.Field2)=True)); 'just effect records with [Primary]=true in the Adress_Tbl for the [Profile_ID_SK] 
  5.  
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.
Jul 12 '18 #21

P: 17
@twinnyfo

that works.
have placed the textbox and it´s source and it displays the amount of addresses that are set to primary.

so should i do something like this now?
Expand|Select|Wrap|Line Numbers
  1. Private Sub chkPrimary_AfterUpdate()
  2.  
  3. Dim PrimaryCount As String
  4. Me.Recalc
  5.  
  6. If IsNull(Me.txtCountPrimary) = True Then
  7. Else
  8. PrimaryCount = Me.txtCountPrimary
  9.  
  10.     If PrimaryCount > 1 Then
  11.         MsgBox "there is 1 or more primarys, you can only have one. Deselect the current primary and then pick a new one."
  12.         Me.chkPrimary.Value = False
  13.         Me.Recalc
  14.  
  15.     Else
  16.     End If
  17.  
  18. End If
  19.  
  20. End Sub
  21.  
  22.  
Jul 12 '18 #22

twinnyfo
Expert Mod 2.5K+
P: 2,532
You're kind of on the right track, but let's really simplify it.

First, txtCountPrimary will never be Null, so there is no need to check for it.

Next, if someone selects a Primary that is not already selected, we want to just deselect any (or all) others.

Finally, we refresh the Form you are on, as well as the Text Box Counter:

Expand|Select|Wrap|Line Numbers
  1. Private Sub chkPrimary_AfterUpdate()
  2. On Error GoTo EH
  3.     Dim lngAddressID    As Long
  4.     Dim strSQL          As String
  5.  
  6.     If Not Me.txtCountPrimary = 0 Then
  7.         lngAddressID = Me.Address_ID
  8.         strSQL = "UPDATE Adress_tbl " & _
  9.                  "SET Primary = False " & _
  10.                  "WHERE Profile_ID_SK = '" & Me.Profile_ID_SK & "' AND " & _
  11.                        "Primary AND " & _
  12.                        "NOT (Address_ID = " & lngAddressID & ");"
  13.         With DoCmd
  14.             .Set Warnings False
  15.             .RunSQL strSQL
  16.             .Set Warnings True
  17.         End With
  18.     End If
  19.  
  20.     Me.Refresh
  21.     Me.txtCountPrimary.Requery
  22.  
  23.     Exit Sub
  24. EH:
  25.     MsgBox "There was an error setting Primary Address!" & vbCrLf & vbCrLf & _
  26.         "Error: " & Err.Number & vbCrLf & _
  27.         "Description: " & Err.Description & vbCrLf & vbCrLf & _
  28.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  29.     Exit Sub
  30. End Sub
And that should take care of it. Let me know if you run across any hitches.

Hope this hepps!

@zmbd:
Please read about The Best Way to Count Yes/No Fields for my justification on using =Sum(IIf(Primary, 1 0)).
Jul 12 '18 #23

P: 17
I have tested your code

I get an error tho on row 10
Expand|Select|Wrap|Line Numbers
  1.                   "WHERE Profile_ID_SK = '" & Me.Profile_ID_SK & "' AND " & _
says that "method or data member not found"....

cant figure out why i get that error.
Profile_ID_SK is located in Adress_tbl
have tripple checked for spelling errors....
Jul 12 '18 #24

twinnyfo
Expert Mod 2.5K+
P: 2,532
Take away the single quotes--my bad in writing.

One of the disadvantages of free-handing code-writing.

Should be:
Expand|Select|Wrap|Line Numbers
  1. "WHERE Profile_ID_SK = " & Me.Profile_ID_SK & " AND " & _
Jul 12 '18 #25

zmbd
Expert Mod 5K+
P: 5,279
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) :)
Jul 12 '18 #26

P: 17
Sry to say, still get the same error.


Expand|Select|Wrap|Line Numbers
  1.     On Error GoTo EH
  2.         Dim lngAddressID    As Long
  3.         Dim strSQL          As String
  4.  
  5.         If Not Me.txtCountPrimary = 0 Then
  6.             lngAddressID = Me.Adress_ID
  7.             strSQL = "UPDATE Adress_tbl " & _
  8.                      "SET Primary = False " & _
  9.                      "WHERE Profile_ID_SK = " & Me.Profile_ID_SK & " AND " & _
  10.                            "Primary AND " & _
  11.                            "NOT (Adress_ID = " & lngAddressID & ");"
  12.             With DoCmd
  13.                 .Set Warnings False
  14.                 .RunSQL strSQL
  15.                 .Set Warnings True
  16.             End With
  17.         End If
  18.  
  19.         Me.Refresh
  20.         Me.txtCountPrimary.Requery
  21.  
  22.         Exit Sub
  23. EH:
  24.         MsgBox "There was an error setting Primary Address!" & vbCrLf & vbCrLf & _
  25.             "Error: " & Err.Number & vbCrLf & _
  26.             "Description: " & Err.Description & vbCrLf & vbCrLf & _
  27.             "Please contact your Database Administrator.", vbCritical, "WARNING!"
  28.         Exit Sub
  29.  
Jul 12 '18 #27

NeoPa
Expert Mod 15k+
P: 30,996
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.
Jul 12 '18 #28

twinnyfo
Expert Mod 2.5K+
P: 2,532
behedwin,

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
  1. 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.
Jul 13 '18 #29

P: 17
Hi

I have been gone for some time. Hopefully this havent become to old.

@twinnyfo
I did as you said.
Expand|Select|Wrap|Line Numbers
  1. 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
  1.                 With DoCmd
  2.                     .Set Warnings False
  3.                     .Run SQL strSQL
  4.                     .Set Warnings True
  5.                 End With
to this

Expand|Select|Wrap|Line Numbers
  1.                 With DoCmd
  2.                     .SetWarnings False
  3.                     .RunSQL strSQL
  4.                     .SetWarnings True
  5.                 End With
and now it works.

so far i think i got everything working as intended.
Attached Images
File Type: png 1.png (27.2 KB, 16 views)
Aug 2 '18 #30

twinnyfo
Expert Mod 2.5K+
P: 2,532
Aaaaaahh yes! It helps when you type the commands correctly!

Did this resolve all issues you were having with this form?
Aug 2 '18 #31

P: 17
Yep, it seems it works just perfectly now.

The error with adding a textbox and then removing it... i dont understand what i fixed, but it seems to have resolved it.
Aug 2 '18 #32

twinnyfo
Expert Mod 2.5K+
P: 2,532
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
  1. 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
  1. 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
  1. 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.

Hope this hepps!
Aug 2 '18 #33

NeoPa
Expert Mod 15k+
P: 30,996
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.
Aug 2 '18 #34

P: 17
Thanks guys.
Learned a bit more during this process. Now to start replicate this to other things, i see tons of potential :D
Aug 2 '18 #35

Post your reply

Sign in to post your reply or Sign up for a free account.