Solved

Table Default not showing in form/MSAccess

Posted on 2010-08-29
40
320 Views
Last Modified: 2013-11-28
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
Comment
Question by:ymf65
  • 17
  • 14
  • 8
  • +1
40 Comments
 
LVL 6

Expert Comment

by:radnbne
Comment Utility
Which version of access are you using?  I just tested this in Access 2007 and it works fine.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility

"( 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
 

Author Comment

by:ymf65
Comment Utility
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
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
"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
 
LVL 6

Expert Comment

by:radnbne
Comment Utility
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
 

Author Comment

by:ymf65
Comment Utility
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
 
LVL 6

Expert Comment

by:radnbne
Comment Utility
that should have been cmbobox1.requery
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
"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
 

Author Comment

by:ymf65
Comment Utility
I tried the Requery and it didn't work
0
 

Author Comment

by:ymf65
Comment Utility
Give me a few minutes and I will tryto upload the file
0
 

Author Comment

by:ymf65
Comment Utility
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
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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
 

Author Comment

by:ymf65
Comment Utility
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
 
LVL 6

Expert Comment

by:radnbne
Comment Utility
Interestingly, when I open this in Access 2007 it works perfectly.  The default values appear as expected.
0
 

Author Comment

by:ymf65
Comment Utility
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
 
LVL 6

Expert Comment

by:radnbne
Comment Utility
The only error I get is a warning about switchboard.
0
 

Author Comment

by:ymf65
Comment Utility
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
 
LVL 6

Expert Comment

by:radnbne
Comment Utility
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

Author Comment

by:ymf65
Comment Utility
Yes.  Does this mean that I have to have the use a table for the row source?
0
 
LVL 6

Expert Comment

by:radnbne
Comment Utility
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
 
LVL 6

Expert Comment

by:radnbne
Comment Utility
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
 

Author Comment

by:ymf65
Comment Utility
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
 

Author Comment

by:ymf65
Comment Utility
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
 
LVL 6

Expert Comment

by:radnbne
Comment Utility
There are no stupid questions here :)

But I might need a minute to find a solution.......
0
 
LVL 6

Expert Comment

by:radnbne
Comment Utility
You can get the current value of the field using this code :

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

0
 

Author Comment

by:ymf65
Comment Utility
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
 
LVL 6

Expert Comment

by:radnbne
Comment Utility

In your test file the code would be

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


0
 

Author Comment

by:ymf65
Comment Utility
Where are you putting that line?
0
 

Author Comment

by:ymf65
Comment Utility
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
 
LVL 6

Accepted Solution

by:
radnbne earned 500 total points
Comment Utility
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
 
LVL 6

Expert Comment

by:radnbne
Comment Utility
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
 

Author Comment

by:ymf65
Comment Utility
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
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
The problem is ... you can't use an OR operation in the Default Value property:

"Albuterol Pump or Nebulizer"

mx
0
 

Author Closing Comment

by:ymf65
Comment Utility
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
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
That's why you don't see it in the query.  How would Access know which value to use ?

mx
0
 

Author Comment

by:ymf65
Comment Utility
Mx was helpful also...I realize that he had to go somewhere, but I thank eveyone that commented
0
 
LVL 6

Expert Comment

by:radnbne
Comment Utility
DatabaseMX

That or is part of the text not a conditional statement :-)
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now