ComBo Box Restriction

[Shamli]

I have to do a combo box restriction. Like if the user didnt select that , error message should pop.
I used this code.

If Me.CmboElement.ListIndex = -1 Then
MsgBox "Please select the Assigned Element"
Me.CmboElement.SetFocus
Cancel = True
End If

Still there is no error message.
What am I doing wrong.
Add on to that. If I remove the restriction, and make a report: already stored value populates!!

[pbaldy]

Try

If Len(Me.CmboElement & vbNullString) = 0 Then

[Shamli]

Tried that. It doesn't work. Previous code which I posted work for other scenarios.
In this case. The combo box doesn't show any values but if I take a report out of it , it shows the previously stored value.
Which I have to restrict.

Try

If Len(Me.CmboElement & vbNullString) = 0 Then

[Micron]

Your problem isn't clear (at least not to me). Nor is your setup.
ListIndex property is the zero based integer that identifies which row was chosen, so I don't see how it could be minus 1.
"I have to do a combo box restriction" means what? Message if nothing is chosen? If a certain value was chosen? Don't allow certain items to be in its list?
Is the combo bound, because if it is, it will often (if not always) have a value in it?

Show more about the code you are trying; the event you're using isn't shown.
Nor do I know how you "take a report" out of a combo box. This control can't contain a report.

[pbaldy]

Is it tied to a multivalue field? I don't use those, and don't really understand what you mean by it showing the previously stored value. Can you attach the db here with instructions on how to recreate the problem?

[Shamli]

The Db is so complicated. I am afraid I can explain it properly or not. I am attaching screenshots this time.
If not, I will attach my db.
Please don't mind.
So the combo box forms looks like this.


Form is a continuous form.
The second row should have been empty because the options I have chosen earlier.(the before form)
But it is taking the values from the previously stored value.

Combo box is bounded:
Bounded Source:
Form Record Source:




And the Combo Row Source is:


The restrictions is in the Combo Row source.
So what happens is: The value is displayed as empty.
But when I click the combo box drop down the restriction is applied (that's perfect).
But if I don't choose anything from the combo box and proceed to the print button which opens a report. instead of empty value I am getting previously stored values.

I really hope I explained something.

Please let me know if I am doing right.

Is it tied to a multivalue field? I don't use those, and don't really understand what you mean by it showing the previously stored value. Can you attach the db here with instructions on how to recreate the problem?

[Shamli]

Lol there is no point in me editing the image I guess. It stores the previous image.

[Shamli]

Am I missing out anything?

Is it tied to a multivalue field? I don't use those, and don't really understand what you mean by it showing the previously stored value. Can you attach the db here with instructions on how to recreate the problem?

[pbaldy]

I don't understand from the image.

[Shamli]

Please find the Db attached.
It opens with Main Menu form.
Where you select " Create New Weekly timesheet button".
1. And then the second menu form " Select weekending to 9/7/2018"
you can see automatically two Element and projects selected in the combo boxes.
And the two projects allotted to the employee on that particular week.
once you click the print button , you see the report.
2. When you select the weekending to 9/21/2018 (Where the employee doesn't have access to the Program Support Project)
the report display an empty element combobox. and the report shows with the value which is wrong because the employee doesn't have access to that project on that week.

You can see the assigned projects in "Add Employee to project" form.


Please let me know if any further information is required.

I don't understand from the image.

[Shamli]

What am I doing wrong here? can anyone help me with this.
Combo box is a bounded one.
I have tried everything. But it still holds the bound values.

I have tried all these codes in the before update event.

If Len(Me.CmboElement & vbNullString) = -1 Then
'MsgBox "Please select the Assigned Element"
'Me.CmboElement.SetFocus
'Cancel = True
' End If


'If Me.CmboElement.ListIndex < 1 Then
'MsgBox "A value is required for ...."
' Me.CmboElement.SetFocus
' Cancel = True
' End If



'If Nz(Me.CmboElement, "") = "" Then
'MsgBox "You must enter a value"
'Cancel = True
'End If


If Len(Me.CmboElement & "") = -1 Then
MsgBox "Please select a Charge Type", vbExclamation, "Required Field"
Cancel = True
Me.CmboElement.SetFocus
End If


'If Nz(Me.ElementID, "") = "" Then
' MsgBox "You Must Fill In Charge Type Field!"
'Cancel = True
'ElementID.SetFocus
'End If
'End Sub

But still no changes. Is sthg wrong with the code?

[Micron]

But if I don't choose anything from the combo box and proceed to the print button which opens a report. instead of empty value I am getting previously stored values.

So you are saying
- that you have a bound combobox, which might have data in it when the form is open to an existing record, but if it doesn't, you want a message and not allow the user to open a report?
- or even if it does have data in it and the user doesn't make another selection, you want a message and not allow the user to open a report?

Should be relatively easy either way.
The first requires checking that the combo isn't Null or contains an empty string (the latter is possible but usually a very remote possibility) in the button click event.
If you also/only want to check that the combo value was changed, you need to compare the OldValue and Value properties. If they are the same, no change was made. The OldValue property is not usable if the control is not bound.

If that's not what you're after, then explain how to use the db and what/when/where you want something to happen. Sorry to say that you seem to provide too much information without revealing what's really needed, and I'll bet you're getting frustrated by the lack of progress. Stick with it and we'll get there.

**Also, please use code tags (see # button on toolbar) for more than a few lines of code. Otherwise, it's too hard to read and sometimes, I just won't bother trying.
EDIT: I see that you posted instructions. I'll try to follow them and see what happens after I download your db.

[Micron]

(Where the employee doesn't have access to the Program Support Project)

This I don't get. If the employee has no week ending value for a project, then why is there a record for that employee in the EmpToProj table and the tempTable?

I think you're problem isn't one of not allowing a report to open if a control on a form has no value, but has to do with faulty records.

I can't say where because I'm not able to follow your design logic, but I suspect one of the forms or the report itself needs to have the record source fixed so that it works as you intend. Maybe EmpToProj should be included in the query for the report, so that you can make a join on the ending date field to some other table so that you only get records that have an ending date in that table?

Some observations that seem suspicious:
- in Current_Timesheet_Report_Q you join the same field(s) to multiple tables. If a value is in tblA and tblC and you also want fields from tblB, you can get a combination of values that really don't exist in real time, but you're manufacturing them because combinations exist in A & B as well as B & C but not in A & C. Especially suspicious when one of them appears to be a temp table. Not sure if that will make sense and it may not be your problem.
- that query is a Totals query, yet you don't use any aggregate functions such as Sum or Count. I don't see a need for anything other than a select query here.
- db normalization appears to be lacking. You're storing data in fields (as in spreadsheet columns) instead of rows; e.g. days of week, UDF's (whatever they are)
- don't repeat data by concatenating it in other fields (e.g FullName). Forms and reports should handle the vast majority of calculations and concatenations.
- modal forms are a pain in the butt when you're developing. They prevent you from getting at related forms/queries.
- while not a huge deal for you maybe, you might want to check out one or more naming conventions. You have a LOT of underscores everywhere; just not my style.
Existing_Timesheet_Report_Q might be qryTSrpt (is there another type of time sheet report besides one that exists? )
Here's a couple of methods if you're interested
https://access-programmers.co.uk/for...d.php?t=225837

http://access.mvps.org/access/general/gen0012.htm

[Shamli]

First, Thank you so much for taking your time and patiently replied to my question.

This I don't get. If the employee has no week ending value for a project, then why is there a record for that employee in the EmpToProj table and the tempTable?

This is because, there is another form which helps in editing the existing timesheets like the previous ones. This is why I have also included the date fields (Start date and end date ) in the EmpToProj table so that we will know which projects is assigned to the employee during which time.

I think you're problem isn't one of not allowing a report to open if a control on a form has no value, but has to do with faulty records.

I can't say where because I'm not able to follow your design logic, but I suspect one of the forms or the report itself needs to have the record source fixed so that it works as you intend. Maybe EmpToProj should be included in the query for the report, so that you can make a join on the ending date field to some other table so that you only get records that have an ending date in that table?

I have kept Temp Timesheet as the row source because some employees are allotted to more than 5 projects. In order to select the projects , elements all the time, that will automatically populate the next time through temp timesheet.
But now because of the restrictions of the employees to the projects , its getting complicated.

Some observations that seem suspicious:
- in Current_Timesheet_Report_Q you join the same field(s) to multiple tables. If a value is in tblA and tblC and you also want fields from tblB, you can get a combination of values that really don't exist in real time, but you're manufacturing them because combinations exist in A & B as well as B & C but not in A & C. Especially suspicious when one of them appears to be a temp table. Not sure if that will make sense and it may not be your problem.
- that query is a Totals query, yet you don't use any aggregate functions such as Sum or Count. I don't see a need for anything other than a select query here.
- db normalization appears to be lacking. You're storing data in fields (as in spreadsheet columns) instead of rows; e.g. days of week, UDF's (whatever they are)
- don't repeat data by concatenating it in other fields (e.g FullName). Forms and reports should handle the vast majority of calculations and concatenations.
- modal forms are a pain in the butt when you're developing. They prevent you from getting at related forms/queries.
-

while not a huge deal for you maybe, you might want to check out one or more naming conventions. You have a LOT of underscores everywhere; just not my style.
Existing_Timesheet_Report_Q might be qryTSrpt (is there another type of time sheet report besides one that exists? )
Here's a couple of methods if you're interested
https://access-programmers.co.uk/for...d.php?t=225837

http://access.mvps.org/access/general/gen0012.htm

I will definitely check them. Thank you for sending me.

A little more explanations of my goal towards this Db. I hope this helps. Please let me know if I am doing anything wrong here.
1. I am doing an advancement in the DB.
2. Before, Employees had access to all the elements and projects. This led in falsely inputting the hours to the projects which they are not assigned to.
3. So what I am trying to do is restricting the employees to the elements and projects what they are assigned to.
4. Instead of having all the elements and projects in the dropdown, they will now have only their allotted ones.
5. That is why I created the EmpToProj Table.
6. I recently added startDate and endDate to the EmpToProj Table.(This is for the EditExisting Form which I Didn copy in thedb attached. This helps in editing the already existing timesheets)
7. Now my problem is because of the temp timesheet. Temp Timesheet carries the recently logged elements and projects in the timesheet. So when this happens , it throws out a null value or a blank combo box when that particular elements is not allotted for the employee in that weekending but was available the previous weekending. Since it is a bound Control value, I am not sure how to proceed.

Once again thank you so much for your detailed explanations. I really appreciate it.

Could you please tell me how to proceed with the db.

[Shamli]

So you are saying
- that you have a bound combobox, which might have data in it when the form is open to an existing record, but if it doesn't, you want a message and not allow the user to open a report?

Omg! yes..
Just throwing out an error message when the user doesn't choose any elements or projects from the combo.
I tried this in many possibilities but nothing is working out.

- or even if it does have data in it and the user doesn't make another selection, you want a message and not allow the user to open a report?

I didn't try this but I am not sure how to do that






Should be relatively easy either way.
The first requires checking that the combo isn't Null or contains an empty string (the latter is possible but usually a very remote possibility) in the button click event.
If you also/only want to check that the combo value was changed, you need to compare the OldValue and Value properties. If they are the same, no change was made. The OldValue property is not usable if the control is not bound.

If that's not what you're after, then explain how to use the db and what/when/where you want something to happen. Sorry to say that you seem to provide too much information without revealing what's really needed, and I'll bet you're getting frustrated by the lack of progress. Stick with it and we'll get there.

Thank you so much for understanding. I have explained the Db but if any further information in required please let me know.

**Also, please use code tags (see # button on toolbar) for more than a few lines of code. Otherwise, it's too hard to read and sometimes, I just won't bother trying.

I will definitely do this next time.

EDIT: I see that you posted instructions. I'll try to follow them and see what happens after I download your db.