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

 

[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?

 

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