Problem with Combo Box Reference Value

[bharatmvs]

Hi,

I have 2 Problems

1. I created a form for a table using Form Wizard. How to automatically point out the form to the last entry in the Table, so that any new record added will be added as a new one, instead of over writing the present one.

As of now, it opens the first record when I open that form and I have to chose last record.

2. I have looked for a combobox search Online.
I created a combo box linking it up with another Table.

I have added the following Code for that combo Box

Dim mycustomer As String
'MsgBox Me.Combo16.Value


mycustomer = "Select * from TOtal details where ([Title] = '" & Me.Combo16.Value & "')"
Me.TOtal_Details_subform.Form.RecordSource = mycustomer
Me.TOtal_Details_subform.Form.Requery

It is returning Index instead of Value.




If i select 2nd element in row, it returns 2, instead of what value it is.

Please advise on the above error and also some good tutorials.

THank You,
Bharat

[rpeare]

You are using a bound form, to go to a new record on a bound form in the ON OPEN event of the form put in something like

docmd.gotorecord ,,acnewrec

Secondly, you do not need to mess with the rowsource of the subform if you are using a form/subform arrangement. You just have to correctly link the main form to the subform using a PK/FK relationship (Primary Key from the main table to the Foreign Key on the sub table).

The subform should, if properly linked, automatically reflect the data related to the main form item.

[bharatmvs]

Hi,
Thank You for a quick response. The kind of search I am looking is, suppose someone chooses Gender as Male in the Combo Box, then all the Records whose value is Male should reflect in my Subform.

My doubt is, why is it referring to Index of the selected, than the selected itself?

[rpeare]

Why are you using a subform if you are not connecting it to data on your main form. The whole point of using a subform is that whatever you type/change on your subform it will inherit the parent record ID and help keep your referential integrity intact.

Just base the main form on a query using the gender as a criteria then just requery the main form in the ON CHANGE or ON EXIT if the unbound search field.

[bharatmvs]

Hi,
Sorry for late reply.

Actually I think I am not clear in putting up the problem I am facing.
The issue is, when I select a Value from a Combo Box, Instead of the value, the index is going as reference.

For Eg. If Singapore, Dresden, Malta are in My Combo Box. If I select Dresden, the return value is 2, but not Dresden because Dresden is no. 2. If I select Malta, it will return as 3.

Even Now, I have used another way to get it done. Set a criteria for a field in QUery as

 [Forms]![Search Form]![Combo0]

. When I choose some value in the combo box, there is no detail Displayed in my Query table. I am sure, even here it is returning Index, but not value.

How to overcome this. Kindly help

[bharatmvs]

Hi,

I am clueless where the mistake is happening. I used macro builder for a button. I have given name of a text field in its Condition. But when I click on the button, it again pops me out with a text Box asking to input Text again, even though I have typed the text already. Whats the difference in MY access? I am using access 2007- 2010.

Thank You

[rpeare]

Whether or not you get results depends on whether you've set up your data structure properly
So for instance, if you are using [forms]![searchform]![combo0], there actually has to be a field on your form with the NAME combo0 and the form name actually has to be SEARCHFORM.

Further, if you want to then use the criteria have to have the BOUND COLUMN set properly. If the BOUND COLUMN of the combo box is a primary key field (which it should be, autonumbers work fine) you have to put the criteria in the field that carries that foreign key.

So for instance
tblCities
City_ID City_Name
1 Dresden
2 Malta

This would be your 'cities' table
on your form SEARCHFORM (don't use spaces or other special characters in your object names, objects are tables, forms, reports, field names, etc) you have a combo box that is named COMBO0, the ROWSOURCE for the combo box is the table tblCITIES. The combo box includes both columns the BOUND COLUMN being the first column (CITY_ID). the COLUMN COUNT would be set to 2, the COLUMN WIDTH would be 0;2 (this will suppress the CITY_ID field but still allow you to use it as a reference).

Then in the table you are trying to query by city you would store the CITY_ID *NOT* the CITY_NAME field and you would put your criteria [forms]![searchform]![combo0] in the CITY_ID field of the table you are querying.