Having trouble with a combo box

[accuss]

Hello. I'm in the process of learning access, and I've got a combo box's data source connected to field of a table. That field holds the value of the billing rate. So, to change the billing rate, just open that table and enter the new billing rate. From there the combo box will reflect the new billing rate.
What I have in place works, as long as you click the down arrow for the combo box to put that value in the combo box.
Is there a way to have that value automatically populate into the combo box, without having to click the down arrow of the combo box? So that when the form that holds the combo box is opened, the combo box has the current value for that field in it. I've tried everything I can think of and everything that I've been able to google.
Thanks in advance.

 

[June7]

Have you bound form to data? What are combobox properties set to: ControlSource, ColumnCount, ColumnWidths, RowSource?

Suggest you provide db for analysis. Follow instructions at bottom of my post.

 

[accuss]

Have you bound form to data? What are combobox properties set to: ControlSource, ColumnCount, ColumnWidths, RowSource?

Suggest you provide db for analysis. Follow instructions at bottom of my post.

Thank you for your reply. Yes, the form that the combo box is on is bound to a table. The combobox properties are set to: store the value entered into a field of the table that the form is set to "BillRate1stHour". The connection to the table holding the billing rate is through it's row source: SELECT [BillRateT].[BillingRate1ssthour] FROM BillRateT ORDER BY [BillingRate1ssthour]; The row source type is table/query Bound column is 1

 

[arnelgp]

table BillRateT must have a Primary (or ID, autnonumber) and that your Billing transaction should save this primary key
instead of the actual value on BillRateT.

combobox should have row source:

SELECT [[BillRateT].ID, [BillRateT].[BillingRate1ssthour] FROM BillRateT;

combo ColumnCount: 2
combo columnWidths: 0, value here

therefore if you change values in BillRateT, your combo refects the changes.

 

[Gasman]

You select the first entry on form load.

set combo value on form load - Google Search

 

[accuss]

table BillRateT must have a Primary (or ID, autnonumber) and that your Billing transaction should save this primary key
instead of the actual value on BillRateT.

combobox should have row source:

SELECT [[BillRateT].ID, [BillRateT].[BillingRate1ssthour] FROM BillRateT;

combo ColumnCount: 2
combo columnWidths: 0, value here

therefore if you change values in BillRateT, your combo refects the changes.

Thank you both for your replies. I've been working on this for hours since. And I was on the verge of replying to you both, explaining that I've tried everything but I'm not getting anywhere. It looks like the solution was to follow recommendation #1 from Arnelgp, and then add the following to the on load :

Private Sub Form_Load()
MyCombo.SetFocus
MyCombo.ListIndex = 0
MyCombo.Dropdown
End Sub


If it's a subform, open that form directly and then go to the on load area

Thank you both!

 

[accuss]

Thank you both for your replies. I've been working on this for hours since. And I was on the verge of replying to you both, explaining that I've tried everything but I'm not getting anywhere. It looks like the solution was to follow recommendation #1 from Arnelgp, and then add the following to the on load :

Private Sub Form_Load()
MyCombo.SetFocus
MyCombo.ListIndex = 0
MyCombo.Dropdown
End Sub


If it's a subform, open that form directly and then go to the on load area

Update.... it does work upon opening the form. But when I type something into a different field, the value that I finally had populating into the combo box disappears. I guess I've got to keep on searching?

 

[June7]

Again, suggest you provide db for analysis.

 

[accuss]

Update.... it does work upon opening the form. But when I type something into a different field, the value that I finally had populating into the combo box disappears. I guess I've got to keep on searching?

I've been looking into what's happening. And it looks like what's made this difficult is that the time (hours) entered are going into a subform of the ticket form. And the subform where all of the action is happening is a continuous form.

 

[accuss]

Again, suggest you provide db for analysis.

Ok here's what I've been working on and asking questions about.
Here's the latest. I thought I figured out the problem I was having. But I was wrong.
********************************************
TicketF- ticket form with ticketdetailF as a subform
TicketF is connected to the TicketT table, the ticketdetailF is connected to the ticketdetailT table
BillRate1sthour variable is coming from the billingT table, the BillingRateID field
the Billratehoursafter variable is coming from the BillRate2nd table, the BillingRate2ID field
the taxrate variable is coming from the TaxRateT table the TaxRateID id
The problem I had with the 1st hour hourly rate and the "hours after the first" seemed to be related to those variables being used in the ticketdetailF. This is because it's a continuous form (the tiketdetailF), and one reference to the labor rates would have to be generated each time an event gets recorded in the work order. But the ones after the first generation were blank.. Seemed unmanageable.
So I moved those variables to the TicketF instead. I was aiming to record the entries for the corresponding fields, coming from their current values in their tables (BillingT, BillRate2ndT, and TaxRateT).
I thought I achieved this because I set the control source of the text boxes to the fields in the table and the row source of each to the corresponding field in the table (BillingT, BillRate2ndT, and TaxRateT). What made it look like this was to set the value of the table fields to the value of the ID that corresponds with the entry I want in the boxes (1). This selects the value from the table and shows it on the box on the form.
I thought I solved my problem but I was wrong. It looked like I did because the form displayed the numbers I have in those tables. But when I go to the table for the ticket form (ticketT) I see the default value of 1 in there. Which is the ID of the default entry from the table with the values. (BillingT, BillRate2ndT, and TaxRateT). So I'm seeing the correct value on the ticket form, but the form table has the wrong value "1".
Can anyone help me out with this? I started over for the 3rd time, after thinking I had it right. But, then I got to this point, and like I said, I realized I was wrong.
Thanks in advance.

 

[accuss]

OK, I figured out that I have to change the field names of the 3 fields (BillRate1sthour, BillRate2nd, and taxrate) to be the same as the ID fields in the tables holding the values that I want in the corresponding boxes. So I changed them to
1stHourRateID, HoursAfterRateID, TaxRateID. I changed the table names to be more specific and I changed the field type to number for all 3. And I believe it's working like the examples that I've found to learn from. But if I go to the tables that store the values, I still see "1" instead of the labor rates. This 1 is from the ID field in the tables that store the values for either rate. Is there a way to store the rate in the box instead of the ID number?
I've attached the file with these changes

 

[Pat Hartman]

I've been looking into what's happening. And it looks like what's made this difficult is that the time (hours) entered are going into a subform of the ticket form. And the subform where all of the action is happening is a continuous form.

ALL forms are single record - ALWAYS. When you change the property of a form to continuous or data sheet view, you are only telling Access to show multiple instances of the "single" form. Each form instance is always only bound to a single record. PERIOD.

The problem I had with the 1st hour hourly rate and the "hours after the first" seemed to be related to those variables being used in the ticketdetailF. This is because it's a continuous form (the tiketdetailF), and one reference to the labor rates would have to be generated each time an event gets recorded in the work order. But the ones after the first generation were blank.. Seemed unmanageable.

Sounds like you are using unbound controls. Remember, each form is always only bound to a single record at a time. But, since there is only one form, there is only one set of properties so for unbound controls, all visible rows will show the same value on every visible row. Only the bound controls will show different values on different rows.

I'll take a look at your sample and continue my post.

 

[Pat Hartman]

It doesn't make sense that first hour and subsequent hour rates just hang out there unrelated to anything. Wouldn't it make more sense if the hourly rates were associated with the service being performed? The rate for database work might be higher than the rate for computer repair?

Tax rate also can't just hang by itself. Tax rates are associated with a taxing entity. Usually State, County, and City for sales taxes. If you sell your services or goods over the internet, the rules become more complicated and you might need to support not only your own local taxing authorities but those of other states as well. Tax rates also vary depending on whether you are selling a product or a service so they are much more complex than you are accounting for.

 

[accuss]

It doesn't make sense that first hour and subsequent hour rates just hang out there unrelated to anything. Wouldn't it make more sense if the hourly rates were associated with the service being performed? The rate for database work might be higher than the rate for computer repair?

Thank you for your replies. Yeah, you're right. It would make more sense like that.

 

[accuss]

Would I just need to add to the table that holds the rates of service? ID2 for another service on the list, with a corresponding rate? ID3 with another service with its rate? And so on?

 

[Pat Hartman]

Overview.
1. Although you have drawn join lines, you have not defined relationships. Please do that also and do it for all tables.
2. Be consistent. Be anal in your consistency. ServiceNames is inconsistent as a table name. Technician in TicketDetailT should be TechID. This takes training. Train yourself because you don't want to have to waste brain power trying to figure out in 6 months is this table using Technician or TechID as the FK name as you are typing code.

Without business rules, it is very difficult to tell you how to set up your pricing. At a simple level, yes, you could add the two rates to the ServiceNames table so that each service has a rate for the first hour and then subsequent hours. But, the concept goes further. Rates change over time. How will you handle that? You probably also need the ability to have multiple rates for each service broken by date range. So, if you decide to raise your rates next month, you probably want to put the new rates in the table NOW but with a future effective date so if you are quoting work for next week, you use the new rates but if someone wants something done today, you use the old rate which is still effective.

Is this a project you are creating to teach yourself database design or are you actually creating an app you intend to use?

Defining all the rules ahead of time cuts down your work dramatically because you don't have to keep changing things as you are developing.

 

[accuss]

Overview.
1. Although you have drawn join lines, you have not defined relationships. Please do that also and do it for all tables.
2. Be consistent. Be anal in your consistency. ServiceNames is inconsistent as a table name. Technician in TicketDetailT should be TechID. This takes training. Train yourself because you don't want to have to waste brain power trying to figure out in 6 months is this table using Technician or TechID as the FK name as you are typing code.

Without business rules, it is very difficult to tell you how to set up your pricing. At a simple level, yes, you could add the two rates to the ServiceNames table so that each service has a rate for the first hour and then subsequent hours. But, the concept goes further. Rates change over time. How will you handle that? You probably also need the ability to have multiple rates for each service broken by date range. So, if you decide to raise your rates next month, you probably want to put the new rates in the table NOW but with a future effective date so if you are quoting work for next week, you use the new rates but if someone wants something done today, you use the old rate which is still effective.

Is this a project you are creating to teach yourself database design or are you actually creating an app you intend to use?

Defining all the rules ahead of time cuts down your work dramatically because you don't have to keep changing things as you are developing.

Thank you for your replies. I will take your advice and put more effort into consistency. You're right. In the future it will be much better if I am looking at something that's consistent in its form. This is just something that I'm using to learn Access. It's a nice challenge for me and I think it's something that I can add on to. One challenge after another. And, at this point, I'm in the beginning stages of it. So the challenges have just begun. One of the challenges of being at this phase, while also learning, is that while I may be finding a solution for the challenge, the solution might end up causing another challenge (due to my lack of experience).

 

[Pat Hartman]

When you attach a price to a product or service, normally you look up the price and then save the current unit price in the invoice or work order. If you go with adding the two rates to the services table, then this is the technique:
1. In the RowSource for the combo that you use to select service, include the two prices. So, the RowSource would be: Select ServiceNameID, ServiceName, 1stHourRate, HoursAfter1stRate (be consistant) From ServiceNamesT Order by ServiceName
2. In the Click event of the combo, you have code to copy the two rates. The columns of the RowSource are a zero-based array so the 3rd and 4th columns are addressed as .Column(2) and .Column(3) respectively

Me.1stHourRate = Me.cboServiceNameID.Column(2)
Me.HoursAfter1stRate = Me.cboServiceNameID.Column(3)

Before you go any further, define your business rules in more detail. Write them down as if you were writing a spec for yourself. They don't have to be perfect or cover every possiblity as they would if you were writing Quickbooks. But you don't want to overlook obvious things such as what I pointed out or you end up with something that doesn't actually work at all. You probably have enough life experience to think of many specific rules if you put your mind to it even though you are not a subject matter expert. You don't need to support any taxes but those of your local state, county, and city but you should support three levels and you may as well create the tables populated with specific state, county, and city names rather than just the generic state, county, and city. Your code will always look up the rate based on the address of your "shop" or the address of your "customer". Either is fine for an example. Just writing your spec is excellent training because you can't do anything at all until you know what you need to do.

The other thing you need to understand for the example you've chosen is how to create a new record and always populate the FK when you started from a different form. So, if you always start a ticket as a stand alone form then you choose the customer from a combo. But, if you start with the customer form and have a button to start a new ticket, now you need to pass in the ID you will be using as the FK. The two methods conflict and you may want to do both so you need to think carefully and plan before you start coding.

The primary thing to remember when you open a data entry form is that YOUR code should NEVER dirty the record before the user does. This simple rule will keep you out of trouble and avoid confusing your user with strange messages. The second thing to remember is that data should always be validated before it gets saved. Sometimes the validation will be complex and sometimes simply checking for presence. Doesn't matter. Do it in the form's BeforeUpdate event. Take a look at one of the videos and play with the sample database (after you watch a video, not before)

Bad Data is Bad for Business #1 and #2

I just added #2 --- that link is further down. It is much shorter than #1. I also added a copy of the database I used for the video. Enjoy:) I created a database to help people to understand how and why to use the Form's BeforeUpdate event to validate data. @Uncle Gizmo and I made a...

www.access-programmers.co.uk

When you use a button to open a data entry form to a new record, use the command that opens the form for data entry. Do NOT add code to open the form bound to a table and then navigate to a new record. You'll see a lot of examples that do this. I don't recommend it. Most people don't implement it correctly anyway and violate rule #1 which is do NOT DIRTY THE FORM with your own code.

When your data entry form is creating related records as it would be when you open the new ticket form from the customer form, then you pass the FK in the OpenArgs and in the form's BeforeInsert event, copy the FK from the OpenArgs to the FK field. The BeforeInsert event does not run until the user types the first character in an empty record. That is the time to populate the FK. NOT before. Let the user complete his intent to create a record before you start helping him