Problem with changing rowsource

[LordLoki]

Hi there i have a weired problem that i can´t figure out on my own.

I have a Form with a combobox and 3 checkboxes

now i want to change the Rowsource of the Combobox by clicking on the checkboxes.

When i click the Checkbox the Rowsource gets Changed as i want it but i can not use the new Values cause its giving me the error "The value you entered is not valid for this field"

The Funny thing is its always bound to the type of data i choose first. When i first select the Week i can select only Weeks (17,18,19 etc)
When i choose Day first it only accepts Dates (01.01.2014 etc)

I dont know what I do wrong here. There is no Control Source set if that helps. And the Bound Column for all Sources is 1

Here the Code

DD_Zeitraum is the Combobox
CB_Day CB_Week CB_Month are the Checkboxes

Private Sub CB_Day_Click()
If Me.CB_Day.Value = -1 Then
    Me.CB_Month = 0
    Me.CB_Week = 0
    Me.DD_Zeitraum = ""
    Me.DD_Zeitraum.RowSource = ""
    Me.DD_Zeitraum.RowSource = Dist_Datum
Else
    Me.CB_Day.Value = -1
End If
End Sub

Private Sub CB_Week_Click()
If Me.CB_Week.Value = -1 Then
    Me.CB_Month = 0
    Me.CB_Day = 0
    Me.DD_Zeitraum = ""
    Me.DD_Zeitraum.RowSource = ""
    Me.DD_Zeitraum.RowSource = "Dist_Woche"
Else
    Me.CB_Week.Value = -1
End If
End Sub

Private Sub CB_Month_Click()
If Me.CB_Month.Value = -1 Then
    Me.CB_Week = 0
    Me.CB_Day = 0
    Me.DD_Zeitraum = ""
    Me.DD_Zeitraum.RowSource = ""
    Me.DD_Zeitraum.RowSource = "Dist_Month"
Else
    Me.CB_Month.Value = -1
End If
End Sub

Thanks for your Help

 

[LordLoki]

Quick Update:

When i set the Bound Column to 0 i dont get the error anymore but then i dont get any values from the list just the Id´s....

I really dont understand what is going on there....

 

[GinaWhipp]

Hmm, what is *Dist_Datum*? How many Columns does the Combo Box have? If more than one what is the Data Type of the Bound Column and what is the Data Type of the underlining field of the COntrol?

Side note: In your case, the Bound Column must be 1, so leave that alone (or put it back)

 

[LordLoki]

The Querys are like this

Dist_Datum = "SELECT DISTINCT (Data.Datum) FROM Data;"
Dist_Woche = "SELECT DISTINCT DatePart('ww',Data.Datum) FROM Data;"
Dist_Month = "SELECT DISTINCT MonthName(DatePart('m',Data.Datum),True) AS Monat, DatePart('m',Data.Datum) AS NMonat FROM Data ORDER BY DatePart('m',Data.Datum);"

Dist_Datum and Dist_Woche both Return one Field
Dist_Month returns 2 Fields cause i need one for Sorting but the first field has the Data i need to be shown.

The Column Count is set to 1
Format is set to nothing

What do you mean by
Data Type of the Bound Column
if thats the Data Type of the resulting column in the query thats always different as you see below

Data Type of the underlining field of the COntrol
dont know what that means too...

Sorry for my dumb questions just getting started with access

The Data looks like this

Dist_Datum:
01.01.2014
02.01.2014
03.01.2014
etc...

Dist_Woche:
18
19
20
21
22
etc..

Dist_Month:
Jan 01
Feb 02
Mar 03
etc...

 

[GinaWhipp]

Okay, so before I post any answers...

Data Type for a field is found in the underlining Table. Go to the Table and go to Design View and you will see the Data Type column. Please post what it says there for the fields listed below.

I need that to reply to your post.

 

[LordLoki]

The Data Type for the Field im Catching (Data.Datum) is Date/Time

In all 3 Cases im getting the same field but i use Datepart to get the Weeknum and Monthname+Datepart to get a string with the Month.

Greets
Loki

 

[GinaWhipp]

Hmm, well that would be the reason for the message...

"The value you entered is not valid for this field"

While you can *show* the DatePart the underlining field is looking for a Date. So, will I can who what week it is based off the Date, I cannot put the week number in the same field because it needs the *Date* not the *week of the Date*. Which also explaines this...

When i first select the Week i can select only Weeks (17,18,19 etc)
When i choose Day first it only accepts Dates (01.01.2014 etc)

So, I guess the question is, do you want to store other values besides the Date? And, why, since you already have the Date and you can gleam those other values via the date.

 

[LordLoki]

Hi Gina,

For me its a little bit strange cause im not storing any Data i just fill a combobox.

When access would search for the *Date* cause thats the Datatype of the underlying table why does it work when i select *week of the Date* first?

Its like he binds the Combobox to the first data i put in. Thats weired for me.

For me i would like to know why its not working anymore cause i had it running without the error for a while. After a network problem i had to use a older backup and afte redoing it i got this error.

For now i found a workaround by creating 3 Comboboxes (one for Date/week/Month) and keep the ones i dont need hidden until the user changes his selection.

maybe i will make a test later by creating a table with Date/Week of Date/ and Month as seperate Fields and try to change the rowsource then.

Thanks for your useful input and help
Greets
Loki

 

[GinaWhipp]

Okay, so your saying it was working but when it stopped is when you created the three Combo Boxes?

Note, if it stopped working after the Network issue, have you looked at the records in the underlining table? Might be a bad one causing the problem. One bad record in a table can cause problems. Also, have you tried Compact & Repair? If that did not work have tried importing all the objects into a clean database?

 

[LordLoki]

No it was Working and then i had to do it again cause i had to use a backup where this step was not done already.

The 3 Combo boxes are my workaround so instead of changing the Rowsource i now use 3 Boxes and hide/Show them as needed.

At the moment im working to get the Form done cause i found a workaround for now. But i will try to find out what was the problem with that thing later.

For your Questions:
I tried Compact and Repair that did not help
i will try moving the Data to a clean database and give you feedback
i will also try to create a table with the week and month as own column and see if rowsource works then.

I will let you know how it worked out

Greets
Loki

 

[GinaWhipp]

Standing by...