Pick Year & Update Combobox which Updates form

[warmanlord]

Hello Gents,

This is my only second post to the forums and have been learning loads from all the different discussions. I am running into a small conundrum, when it comes to a form. I added in a combobox drop down that I used the wizard to find a record on my form based on a value I selected in my combobox. This works wonderfully as you can see in the attached pictures.

I have however come to realize that this combobox only will work for a time period and here is why. The values in the box are V-1-2015, which will incrementally increase over the year and the forth coming years, so you will end up having a drop down menu after a few years that spans say v-1-2015 to v-200-2015 and v-1-2016 to v-200-2016, etc... This will make the combox extremely large and cumbersome after a number of years.



The last digit is the year, the V never changes and the incremental number changes as you add to the database. What I am trying to figure out how to do is either use a TEXT box that will limit the choices in the combobox that populates the forum with information or some variation of that. Where you can type in the year, say 2015 and it will only allow the combobox to list the records ending with 2015 and not showing the other records thus eliminating a lot of clutter. Then once you select the record the combobox populates the form with information.

Thanks again for any suggestions or help. If I can clarify or enhance the information please let me know what is needed and I will do that.


Attachment 22341

[June7]

Sounds like cascading (dependent) comboboxes. Review http://datapigtechnologies.com/flash...combobox2.html

[warmanlord]

Thanks, I was able to watch that video and got the combobox setup, but I am not great with the code that would need to be input into the query builder where the first combo box has the year, say 2015 which is then the second updates based on the year. What I have so far is [forms]![Video Form].[selectyear.cbo], what i have to do is since the year selected is 2015 is it should look at v-1-2015 and all records that contain 2015 should be listed after the requery. Might have to watch more videos on query and code building.

[June7]

combobox name is [selectyear.cbo]? Unusual naming convention. Most would use cboSelectYear.

Advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

The second combobox RowSource could be:

SELECT [Video Number] FROM table WHERE [Video Number] LIKE "*" & [selectyear.cbo];

[warmanlord]

sorry about that it was to be selectyear_cbo, I am not extremely traditional when it comes to naming conventions, this is just a small project I do from time to time, but I am wanting to get more into it, so I should use the more conventional cboselectyear, but I tend to right it how I say it in terms of select year combobox. But I will take a look at the code your provided and see if I can get it working correctly. Also, as I am still in my mind a novice when it comes to coding and programming within access, do you have any suggestion on like video tutorials that would provide a solid base/founding to build upon when it comes to coding within access. I was thinking of signing up for lynda.com to go through their tutorials as I did long ago, i know a few free ones and some youtube videos, but I want to become proficient with it.

[warmanlord]

I have attached a picture of what I did this morning, the code ran, but I was unable to get it to show the records.


Attachment 22349

[warmanlord]

http://www.fmsinc.com/microsoftacces...cascading.html

reading over this might get me closer, will check it out.

[warmanlord]

OK so after reading that document, I took out the information in the expression builder and went to the VB coding, attached is what Input, but it sent back an error and debug.


Attachment 22352

[warmanlord]

I forgot to add the table name, but I did that, and i removed Me.SelectYear_cbo.rowsource to Me.VideoNumber_find.Rowsource =

[warmanlord]

Updated code information..

Attachment 22354

[June7]

I suggest you put the SQL statement directly in the combobox RowSource. However, significant error in my suggested SQL. Try:

SELECT [Video Number] FROM VideoCameraInformation WHERE [Video Number] LIKE "'*" & [SelectYear_cbo] & "'";

Then the code only has to be:

Private Sub SelectYear_cbo_AfterUpdate()
Me.VideoNumber_find.Requery
End Sub

In VBA, the SQL construction would be:

= "SELECT [Video Number] FROM VideoCameraInformation WHERE [Video Number] LIKE '*" & Me.SelectYear_cbo & "';"

[warmanlord]

Here is the updated, now in you code was ' to be between the "", unless my eyes are decieving me. I added the code, but once i go back to the form the code then changes itself and when i click on the selectyear_cbo it then gives me a popup with ' in it.

Attachment 22355

[June7]

Okay, correction. Just did some testing. My original SQL without the apostrophes was correct. So in the RowSource property:

SELECT [Video Number] FROM VideoCameraInformation WHERE [Video Number] LIKE "*" & [SelectYear_cbo];

However, the code for the VBA construction is correct. So is this version:

= "SELECT [Video Number] FROM VideoCameraInformation WHERE [Video Number] LIKE '*' & [SelectYear_cbo];"

[warmanlord]

I am not sure if I am messing this up more as I go, but here is what I have. Before I started the VideoNumber_find Combobox worked allowing you to select the videonumber in ascending order, but since I have added the other combox that has a listing values of 2015, 2016, 2017 etc and add in the code that we have been talking about, I think I keep breaking it lol.



Attachment 22356

Attachment 22357

[June7]

Well, the code is searching for ID, not VideoNumber. This means the dependent VideoNumber combobox RowSource would have to be a query that includes the ID field and the VideoNumber would display as lookup alias. Will have to use VBA to set the RowSource property.

= "SELECT ID, [Video Number] FROM VideoCameraInformation WHERE [Video Number] LIKE '*" & Me.SelectYear_cbo.Column(1) & "';"

Also, set other combobox properties:
ControlSource: ?????? - is this needed, what dataset are you searching? What is purpose of form these comboboxes are on?
BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0";0.5"

Dependent comboboxes with lookup alias do not work nice in Continuous or Datasheet bound form if the comboboxes are for data entry. If neither of these comboboxes is bound (used only to enter search criteria to filter records) should be okay.

If you want to provide db for analysis, follow instructions at bottom of my post.