using two combo boxes

[mcguires99]

Hi very new to access so really not to sure what I'm doing!



Have a form with 2 combo boxes whose results when combined need to give a third value to be placed automatically in the same form. Obviously different values can be achieved depending on the different selections made in the combo boxes.

Is using the combo boxes the correct way to do it and if yes how is this done?

Really wish the person completeing the data entry on the form to see the result from the 2 combo box selections made.

Hoping someone can help.

[hertfordkc]

However, using 2 combo boxes may be the best way to accomplish your task. It appears that you want to use the contents of the 2 combo boxes to retrieve a record or records. You didn't mention if the form was based on a query, so I'm assuming that
you can specify a query which contains the fields you seek. When in this situation, the field might contain one of many possible records and you want the combo boxes to limit the field(s) displayed. If this is where you are headed, then you can use the combo boxes to construct a filter which will act in conjunction with the form query to give you the desired result.
You might do something like this.
Open the form with only the first combo box visible (see the visible property on the property sheet of the control in design view).
When the user makes a choice in the first box (afterupdate or onclick event), make the second combo box visible.
I do this with Visual Basic.
When the user makes a choice in the second box, the event code would construct the filter and requery to show the filtered results.
The following link is a very simple example of one way of using VBA in conjunction with controls.
http://https://www.accessforums.net/attachment.php?attachmentid=4972&d=1319831849

[mcguires99]

Hertfordkc thanks for very quick reply very impressed!
Yes I didnt really give much in the way of useful info!

Both combo boxes take a destination "from /to" selection from same table (tblDestinations), however depending on which start/to values are chosen in the two seperate combo boxes, "From" & "To", I wish to automatically have assigned a value in days from a table (tblContracted Days).

i.e I require:-

If I choose a "From A" location to a "To D" destination to give it a particular value from (tblContracted Days) and of course

If I choose a "From A" location to a "To C" destination to give it a different value from (tblContracted Days).
Having this value, in days added to my "Contracted Days" field in the same form. Unsure if this makes sense??

Also, your link would not open for me regardless of what I tried.

Thanks again -- I need your help!!

[hertfordkc]

Please look in this thread to see if you can load Myformtest.zip
https://www.accessforums.net/forms/f...xes-18492.html
From your last post, I'm certain that you are on track. However, the devil is in the details and I still don't understand enough of your particular requirements to write an example for you.
Please see if you can look at the link, or failing that, expound on
[QUOTEIf I choose a "From A" location to a "To C" destination to give it a different value from (tblContracted Days).
Having this value, in days added to my "Contracted Days" field in the same form. Unsure if this makes sense??][/QUOTE]

[mcguires99]

will try to explain further. I have a single form with two combo boxes "From" and "To" which allow selection of place names from a single table, tblplacenames.
Depending on which two names are selected I wish to enter a value in days to a field "Contracted Days" in the same form. eg if "From"=London and "To"= York Contracted days is 12.

eg if "From"=Hull and "To"= Farnham, Contracted days is 18.

What other table or additional mods do I need to effect this type of result? As I said very new to Access

THX

[hertfordkc]

and TO destinations in the combo boxes?

If so, you only have to filter on tblContractedDays to display the contracted days, assuming that tblContracteddays contains the contracted days for each origin-destination combination available from tblPlaceNames.
Before giving you another generalized description of what you need to do, were you able to locate "MyFormsTest.zip"?
Were you able to download it? Were you able to download it but couldn't unzip it?
If you could look at that example, I think we would have a much clearer dialogue.

On what is your form (containing the combo boxes) based, i.e. what is it's recordsource?

[mcguires99]

hi wasable to open your example but didnt undestand it. As I said very new to access. Now taking a new tact.

tblTripNumbers contains various fields including trip number (Text Field), delivery times (in number of days), FromTo (place names), Contracted days (Number of days), etc.

tblFromTo (used for my combo box) contains 3 columns.
C1 is ID, C2 is FromTo (which are place names), C3 is Contracted days (in number of days).

When combo box used in "frmTripnumberdurations" a destination is chosen from the place names shown in the drop down. Currently this puts the place name in a field on the same form titled "Contracted Days".

I require when a specific destination is chosen that the number of contracted days is shown in this "Contracted Days " field on the form and that when the record is saved the results populate all the fields in tblTripNumbers

Thanking you for your patience thus far

[hertfordkc]

hi wasable to open your example but didnt undestand it. As I said very new to access. Now taking a new tact.

tblTripNumbers contains various fields including trip number (Text Field), delivery times (in number of days), FromTo (place names), Contracted days (Number of days), etc.

tblFromTo (used for my combo box) contains 3 columns.
C1 is ID, C2 is FromTo (which are place names), C3 is Contracted days (in number of days).

When combo box used in "frmTripnumberdurations" a destination is chosen from the place names shown in the drop down. Currently this puts the place name in a field on the same form titled "Contracted Days".
I don't think the changes were necessary, but they should work. Does this last sentence mean that you are getting the correct row, but not the info which you desire??? If so, you may not have specified the number of columns and bound column correctly in your combo box.
I require when a specific destination is chosen that the number of contracted days is shown in this "Contracted Days " field on the form and that when the record is saved the results populate all the fields in tblTripNumbers
Take it one step at a time. Display your selection results by using a msgbox or debug.print. When you know you have correctly selected the appropriate record, then we can focus on transferring the result to the record.

Thanking you for your patience thus far

Are you comfortable shifting to design view and form view and Visual Basic? All the example was intended to show was how VBA interacts with listboxes or comboboxes, and use the results to run a query.

[mcguires99]

Dont know how to use msgbox which doesnt list in access help nor do I know how to use debug.print.

However all fields on form now populated correctly. No Errors!!! However data on 2 of these fields (Contracted days, a calculated field) and (Demurrage Days, a numbers field) both in tblTripNumbers do not populate in the table and of course therefore do not show in any report. The values only exist on the form

[mcguires99]

Further to my reply yesterday. I find today when going back into my form which I have not altered since yesterday that I now have #Name? in both the contacted days and Demurrage text boxes with the form now not behaving as it did yesterday. Whats happening?

[hertfordkc]

Dont know how to use msgbox which doesnt list in access help nor do I know how to use debug.print.

However all fields on form now populated correctly. No Errors!!! However data on 2 of these fields (Contracted days, a calculated field) and (Demurrage Days, a numbers field) both in tblTripNumbers do not populate in the table and of course therefore do not show in any report. The values only exist on the form

----------------------------------------------------------------------------------------------------------------------------

1) Dim I as integer
I = Msgbox("message", vbokcancel, "Title")
Put variables or text as you desire in "message" and "title"
2) debug.print var1, var2...."text", varxx ,/// results show in the immediate window of the VBA screen
3) If you can calculate contracted days and demurrage days, do you really need to save them in a table? If you can calculate a result, it is generally recommended that you not store it in a table.

In your last message, you said the boxes which you were associating with calculated fields were showing #Name? errors. Did you change the names on the query, or not have a fully qualified reference in the control's source?

[mcguires99]

I think you dont understand what I mean when I say NEW to Access. Have no idea what you mean by your points 1 & 2.
Where if not in my table do I store the calculated results shown on my form? I wish to run a report based on the calculated results and the other info as entered on the form.

I have made no changes whatsoever on the form from one day till the other and now I get the #Name? error showing on my fields in the form. If I delete the fields and start again I get them to correctly calculate the values. However when I log off then log-on next day I get the error messages!!! You ask if I changed the names on the "query". Which query would that be ? I have not created a query!! Is that whats missing?
Please help

I'm simply making a selection from a combo box showing the result on my form and wishing the said result to appear in my database table in order to run a report.

[hertfordkc]

My apologies. I prolonged the problem by dismissing your newness.

I think you dont understand what I mean when I say NEW to Access. Have no idea what you mean by your points 1 & 2.
My points 1 and 2 were directed to ways to debug in Visual Basic, which I thought you were contemplating because of your proposed use of the combo boxes.
Where if not in my table do I store the calculated results shown on my form? I wish to run a report based on the calculated results and the other info as entered on the form.
When you are new to Access, it seems a waste to be able to calculate something and not store it. I struggled with that. However, you will find that queries provide a powerful and flexible way of tying forms and reports to tables. Even though basing a form or report is perfectly acceptable, I usually create a query even if it merely mirrors the underlying table. I'll speak more to this and your particular problem below.
I have made no changes whatsoever on the form from one day till the other and now I get the #Name? error showing on my fields in the form. If I delete the fields and start again I get them to correctly calculate the values. However when I log off then log-on next day I get the error messages!!! You ask if I changed the names on the "query". Which query would that be ? I have not created a query!! Is that whats missing?
In your earliest posts, I thought that you were doing a calculation on "contracted days". Queries allow you to do calculations with minimal understanding of Visual Basic. It provides Excel type functions that allow you to create new fields based on the values your table fields.
Please help

I'm simply making a selection from a combo box showing the result on my form and wishing the said result to appear in my database table in order to run a report.

__________________________________________________ _____________________
And here is the crucial part of our exchange. When you click on the combo box, you
are selecting something that is special to your report. Is it 1) the "placename" combination that limits your report to stuff related to that "placename", 2) the "contracted days" that are specific to that "placename" which will be used in further calculations on the report, 3) something else.
"1" implies a filter so that you are reporting on a subset of the "fromto" combinations in your table. This suggests creating a report based on your table (or a query) and telling the report to look back at the box on the form to get a piece of information for a filter.
"2" is using a number from a previous form as an additional piece of information in some calculation.
"3" ...Maybe you really don't want a report at all. You are want to call up FromTo or Tripnumber, fill in days of some sort, calculate other days and save the record for other use???

[mcguires99]

Sorry but really cant make it any clearer. 2 tables. 1 is tbltripNumber from whose data when populated I will create a report. 2nd table tblFromTo which contains 3 fields 1 is ID 2 is place names and 3 is contracted days. This table used for my combo box. when a place name is chosen from combo box a resulting number of contracted days is placed in another field - "Contracted Days" on the form frmTripNumberDurations. This all works fine excet the contracted days doesnt fill in the tblTripNumber but does on the form. Likewise the Demurrage days also correctly computed and shown on the form also does not populate in the tblTripNumber.

This plus the form showing #Name? when ever I visit it the next day after it working fine the previous day are the two problems.

Some code around control and record source for the various fields I think is whats required as the theory is forgein to me.