Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 330
  • Last Modified:

Table Default not showing in form/MSAccess

I have a form with bound combo boxes.  I set default values in table design view for the respective fields but the default values do not show up in the comboboxes when I launch the form. ( P. S. I can't set the defaults directly from the comboboxes because the control source for the combo boxes changes depending on certain events.)
0
ymf65
Asked:
ymf65
  • 17
  • 14
  • 8
  • +1
1 Solution
 
radnbneCommented:
Which version of access are you using?  I just tested this in Access 2007 and it works fine.
0
 
Jeffrey CoachmanMIS LiasonCommented:
Default values are really designed to work in Bound controls.
Yes, you can have a default value in an unbound control, but IMHO, it works against the meaning of a default Value.

A Default value is a Value that will "Appear" in a field as soon a you create a "New" record.

So can you take a step back and first explain to us what it is you are trying to accomplish here?

Perhaps there is an alternate method...

Lets see what some other experts say...

;-)

JeffCoachman
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:

"( P. S. I can't set the defaults directly from the comboboxes because the control source for the combo boxes changes depending on certain events.)"

Why can't you use the Form BeforeInsert event (for New records) and Form Dirty event for existing or new records.  And the Form Current event to handle  "the control source for the combo boxes changes depending on certain events."

?

mx
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
ymf65Author Commented:
I am using Access 2003.  

I am using the same combobox in two different circumstances.  In one circumstance the combo box's controll source is "[controller]", in another circumstance the combo box's controll source is "[Rescue]".  
For Example a simplified version of the code would be:
If Color = "red" then
cmbobox1.controlsource = "[Rescue]"
else
cmbobox1.controlsource = "[controller]"
Endif
The I need the Rescue Field to have one default and the Controller Field to have a different default.  

I noticed that the defaults are showing in the table but not in the query that the form is pulling data from.  Does that explain it any better?
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
btw ... I'm not a fan of default values at the table or form control level because ... when you do come to the New Record position,  you see values hanging in those controls, while other controls of course are blank ... so overall, it *looks* like you are on an existing record!  It's much cleaner to set any Default Values via the Form BI and/or Dirty events.

AND ... it's not that hard to accidentally to wipe out a default value in a control or table field.

mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"The I need the Rescue Field to have one default and the Controller Field to have a different default.  "

That can all be done via the Form BI and/or Dirty event in conjunction with the Form Current event.

mx
0
 
radnbneCommented:
try adding a reqeury to the combobox after setting the recordsource

If Color = "red" then
cmbobox1.controlsource = "[Rescue]"
else
cmbobox1.controlsource = "[controller]"
Endif
cmbobox.requery
0
 
ymf65Author Commented:
Hi DatabaseMX:
I know that I tried the before insert event at one point.  To be honest I cant recall right now what problem I had with that.  I may revisit that idea if I cant get it to work otherwise.  It just seems to me that if the the default values that do show up in the tables thaen i should be able to get the form to see them.  I think that the problem lies in the fact that the query is not showing the default values.

I could put two combo boxes right on top of each other and turn one on or off depending on the condition and have each bound to a different field, but that seems messy.  I guess I am just looking for a clean solution
0
 
radnbneCommented:
that should have been cmbobox1.requery
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"I guess I am just looking for a clean solution"
Well, I told you my 'clean' (looking) solution, lol.

"It just seems to me that if the the default values that do show up in the tables thaen i should be able to get the form to see them. "

Well, of course you should ... something else is going on.  

Can you:

1) Compact & Repair (*** to shrink the size),

2) Zip up the MDB (*** to further shrink the size)

3) Attach the file for upload here (using the 'Attach File function below) ... removing any sensitive data of course.

4**** And please give a clear explanation of exactly how to reproduce the problem or what you are trying to do.

NOTE:  If you have an A2007 ACCDB, please convert to A2003 MDB.
mx
0
 
ymf65Author Commented:
I tried the Requery and it didn't work
0
 
ymf65Author Commented:
Give me a few minutes and I will tryto upload the file
0
 
ymf65Author Commented:
Hi Mx,
I was trying to remove all the sensitive info before uploading but there is too much.  I will create a similar dummy database  and then upload that one but it may take maybe a 1/2 hour.  Sorry for the delay.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
No problem ... I have to go out for a couple of hrs also.

Be *sure* what you uploaded exhibits the problem, and specify exactly how to replicate ...

mx
0
 
ymf65Author Commented:
Hi Mx,
Finally uploaded a stripped down version of the database.  If you go into the tables called "Yellow Zone" and "Red Zone" you will se that a default value exists for the fields "RescueMedicine" and EmergencyRescueMedicine".  

If you open the form and go to the first blank record and then click on the Yellow Zone tab or the Red Zone tab, the default values do not show up.  Also they are not showing up in the Query.
Test.zip
0
 
radnbneCommented:
Interestingly, when I open this in Access 2007 it works perfectly.  The default values appear as expected.
0
 
ymf65Author Commented:
That is so weird!!  I have know idea why it wont work on 2003.  

I just realized that I accidently erased some important code lines when I tried to scale down the database so if anyone is getting weird errors that is why.  I will fix it and upload a better working version.

Thanks for trying to help.  At my office we have Acess 2007.  I will try the real version with 2007 tomorrow and see what happens.  This whole thing is puzzling.  
0
 
radnbneCommented:
The only error I get is a warning about switchboard.
0
 
ymf65Author Commented:
Actually you are correct.  I deleted the switchboard since it had too much identifying info on it -but deleating that doesn't create any problems using the forms.  

 I erased some code that defined which color tab you were on, but actually, after I sent my previous message, I realized it was not important anymore.  The code that needed to know which color you were on was also erased.  So it should work ok after all.

A question for you: when access 2007 brings up a new blank record and you click on the yellow or red tab does the default show automatically or do you have to first start typing.  I am asking because, I tried the before update event for the form and I can set the value of my combo box to my default value but you must first type at least one character for the default value to appear.  I think that this is how the before update event works.  I would prefer for the values to just be there as soon as you go the a blank record.

0
 
radnbneCommented:
Sorry I was wrong, I spoke too soon.

I was seeing a value but then I realised I had not progressed far enough through to the blank record.

Looking at the code I think I see what is happening.

You are setting the control source and then updating the rowsource manually with a list.  The will remove disable the default value.  I assume that you want the default value from the table to be selected from the list.

Is this correct?
0
 
ymf65Author Commented:
Yes.  Does this mean that I have to have the use a table for the row source?
0
 
radnbneCommented:
No the problem is that you are using the query for the control Source.  The query does not expose the Default value for the Field, and therefore it is not available.

0
 
radnbneCommented:
You said you can't set the Default value in the control cause it changes.  But you are then creating the Rowsource list in code.  Why not set the default value there?
0
 
ymf65Author Commented:
Is there anyway for the query to see the default values or would I have to make one huge Table that holds all of my fields and have that Master Table be the control source for the form?  

I am new to Access, so I appologize if my questions seem stupid.
0
 
ymf65Author Commented:
I tried setting the default value in the code and it didn't work.  Perhaps I did something wrong.  I think that in the same place as the rowsources were defined, I put a line like:
cmboField1.DefaultValue = "My default value".  I will try again.
0
 
radnbneCommented:
There are no stupid questions here :)

But I might need a minute to find a solution.......
0
 
radnbneCommented:
You can get the current value of the field using this code :

Result = CurrentDb.TableDefs("YellowZone").Fields("RescueMedicine").DefaultValue

0
 
ymf65Author Commented:
Take your time.  I am grateful for the help.  When I put in the line that I mentioned that trys to define the default value in code I get a   #Name?   error in the cmbobox.
0
 
radnbneCommented:

In your test file the code would be

Me.cmboField1.Value = CurrentDb.TableDefs("YellowZone").Fields("RescueMedicine").DefaultValue


0
 
ymf65Author Commented:
Where are you putting that line?
0
 
ymf65Author Commented:
The code definately puts the default value of the table into the combo box.  However, if you pick a different value from the dropdown menu of the combo box and then move to another tab, the value that you picked gets replaced by the default value if you return.
0
 
radnbneCommented:
In your lblTabYellow_Click() sub Routine

Private Sub lblTabYellow_Click()
   
    Me.BoxPatchGreen.Visible = False
    Me.BoxPatchYellow.Visible = True
    Me.BoxPatchRed.Visible = False
   
    Me.boxMain.BackColor = 7467773
    Me.cmboField1.SetFocus
    Me.lblField1.Caption = "Rescue Medicine:"
    Me.cmboField1.ControlSource = "[RescueMedicine]"
    Me.cmboField1.Requery
    Me.cmboField1.RowSourceType = "Value List"
    Me.cmboField1.RowSource = "Albuterol Vials; Albuterol Pump and Spacer; Xopenex Vials; Xopenex Pump and Spacer; Albuterol Pump or Nebulizer; Xopenex Pump or Nebulizer; MaxAir"
    Me.cmboField1.DefaultValue = CurrentDb.TableDefs("YellowZone").Fields("RescueMedicine").DefaultValue
   
End Sub
0
 
radnbneCommented:
It will if you don't save the record.  Because it's the default value.

If you are wanting to keep the selection the person made, then you need to save the record once they select an option.
0
 
ymf65Author Commented:
OMG!!! It works!  I cant thank you enough.  That is some pretty amazing code.  I will click the button for accepting your solution.  Thank you for putting so much time into this!
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
The problem is ... you can't use an OR operation in the Default Value property:

"Albuterol Pump or Nebulizer"

mx
0
 
ymf65Author Commented:
I spent hours searching the net for a solution and couldn't find anything.  radnbne spent a ton of time helping me and was very patient with my level of coding experience.  I cant that this person enough!
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
That's why you don't see it in the query.  How would Access know which value to use ?

mx
0
 
ymf65Author Commented:
Mx was helpful also...I realize that he had to go somewhere, but I thank eveyone that commented
0
 
radnbneCommented:
DatabaseMX

That or is part of the text not a conditional statement :-)
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Huh ?  Sorry, but Access does not interpret it that way :-)

Change it to either

"Albuterol Pump"
or

"Nebulizer"

and it will so in the query ... just for starters :-)

mx
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 17
  • 14
  • 8
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now