Records not being identified (1 Viewer)

&Boom

Registered User.
Local time
Yesterday, 19:39
Joined
Sep 16, 2013
Messages
21
I have a form with a subform. The form has 2 controls, Preview and Submit. The submit button moves the records users are entering (Date, Company, Hours) from a Temp table to a ServiceTbl.

The users are allowed to select "?????" in company in case they don't know it at the present time. At the end of the month when all of these records will be 'Submitted' to the ServiceTbl, I need to check the value of each Company ComboBox on the Subform for the "?????" entries.

I tried the code:

If Me.CompanyCbx.Text = "?????" Then
MsgBox("Enter correct company.")
End If


However, that only works on 1 record, and that record has to be selected on the subform. If a new row is clicked, as in the user is going to enter new data in, but chooses not to, it still doesn't work.
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 16:39
Joined
Aug 30, 2003
Messages
36,133
If you're checking the entire temp table, you could use a DCount() to check to see if there are any records in it with that value.
 

&Boom

Registered User.
Local time
Yesterday, 19:39
Joined
Sep 16, 2013
Messages
21
Paul, that is the direction I went. However, I am not familiar with DCount(). I have a "Check Company" button that opens a new form with all Companies ID that match "?????".. Which is fine, however - stick with me here.

On the original form where they are inputting hours and selecting company. Say the first record is O.K, and the second record is ????? .. If the user clicks on the 3rd row to start a new record, the company doesn't show up in the combobox on the Check Company Form. It is as if it is still referencing something on the old form. How can I fix that?
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 16:39
Joined
Aug 30, 2003
Messages
36,133
Can you post the db here?
 

&Boom

Registered User.
Local time
Yesterday, 19:39
Joined
Sep 16, 2013
Messages
21
Unfortunately, it contains users names which I can't disclose. I will attach screen shots to help, which I hope they do!
 

Attachments

  • InputHours.PNG
    InputHours.PNG
    16.5 KB · Views: 86
  • UnkownFrm.PNG
    UnkownFrm.PNG
    18.2 KB · Views: 93
  • Alert1.PNG
    Alert1.PNG
    26.9 KB · Views: 92
  • ChangedLOB.PNG
    ChangedLOB.PNG
    19.1 KB · Views: 88
  • Finish.PNG
    Finish.PNG
    15.2 KB · Views: 93

&Boom

Registered User.
Local time
Yesterday, 19:39
Joined
Sep 16, 2013
Messages
21
I fixed my first issue. Now, when I open all records from the table with "?????" in them, the appear in a new "UnkownFrm." This makes it easier to locate records that need to be fixed. However, when they are clicking "Save/Close" on the "UnknownFrm" signifying that they have fixed all "?????" records to a real company, my code is only checking one row of data, not all rows.

My code is:

Me.ServiceLOBCbx.SetFocus
If Me.ServiceLOBCbx.Text = "?????" Then
MsgBox ("Please Select Appropriate line of business.")
Me.[ServiceLOBCbx].SetFocus
End If

Is there a way to make Me.[Object].Text select ALL records to make sure they ALL must be changed, and not just one?

Sorry if this is hard to follow, I am not sure how to word it better!
 

pr2-eugin

Super Moderator
Local time
Today, 00:39
Joined
Nov 30, 2011
Messages
8,494
How to Upload a Stripped DB.

To create a Sample DB (to be uploaded for other users to examine); please follow the steps..

1. Create a backup of the file, before you proceed..
2. Delete all Forms/Queries/Reports that are not in Question (except the ones that are inter-related)
3. Delete auxiliary tables (that are hanging loose with no relationships).
4. If your table has 100,000 records, delete 99,990 records.
5. Replace the sensitive information like Telephone numbers/email with simple UPDATE queries.
6. Perform a 'Compact & Repair' it would have brought the Size down to measly KBs..
7. (If your Post count is less than 10 ZIP the file and) Upload it..

Finally, please include instructions of which Form/Query/Code we need to look at. The preferred Access version would be A2003-A2007 (.mdb files)
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 16:39
Joined
Aug 30, 2003
Messages
36,133
Is there a way to make Me.[Object].Text select ALL records to make sure they ALL must be changed, and not just one?

Well, I'm back to recommending a DCount() to test the table:

If DCount("*", "TableName", "FieldName = '?????'") > 0 Then
 

&Boom

Registered User.
Local time
Yesterday, 19:39
Joined
Sep 16, 2013
Messages
21
Paul, I examined the usefulness of DCount , and I am not sure I quite understand it.

Here is the code I used.

If DCount("*", "ServiceTempTbl", "ServiceTempLOB_ID = 35") > 0 Then
MsgBox "Please correct all lines of business."
End If

Now, when I correct the "?????" entries and press Save/Close, it still shows the MsgBox.
 

&Boom

Registered User.
Local time
Yesterday, 19:39
Joined
Sep 16, 2013
Messages
21
Paul, it appears with the DCount function I don't even need that new form to open. Simply putting that code in VBA behind the Submit button seems to work.

Thank you - very much.
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 16:39
Joined
Aug 30, 2003
Messages
36,133
Happy to help.
 

&Boom

Registered User.
Local time
Yesterday, 19:39
Joined
Sep 16, 2013
Messages
21
Paul, I may (or may not) have another issue with this. Now, the end users are inputting hours and companies for different people, whose information is also being stores in the TempTbl. If John Doe has all legitimate companies, but John Smith has one "?????" .. It will still throw the error to choose correct company. Is there a workaround for this so it references the rows of data by Employee_ID?

Basically, if one person's report is good to go, but another has unknowns - it is preventing all of them from being submitted. How can I narrow it down to only those who have "?????" to restrict submitting?

I'll attach a PNG of the ServiceTempTbl if that helps.

Thanks
 

Attachments

  • Capture.PNG
    Capture.PNG
    9.5 KB · Views: 90

&Boom

Registered User.
Local time
Yesterday, 19:39
Joined
Sep 16, 2013
Messages
21
I tried this and it did not work:

If DCount("ServiceTempStaffID", "ServiceTempTbl", "ServiceTempLOB_ID = 35") > 0 Then
MsgBox "Please correct all lines of business."
 

&Boom

Registered User.
Local time
Yesterday, 19:39
Joined
Sep 16, 2013
Messages
21
So, a DLookup for Staff_ID and a DCount for LOB_ID = 35?

I am getting confused on how to execute it. I am not very good with VBA at all. I can conceptualize it, but not code it.

So if an Employee_ID has a Count > 0 of LOB_ID = 35 then "Error" for and only for that person Else Submit..
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 16:39
Joined
Aug 30, 2003
Messages
36,133
Conceptualize a single DCount with 2 criteria, in essence "count the records where the employee ID matches this employee AND the other field = ?????". So it would look like this, presuming the existing test works:

DCount("*", "ServiceTempTbl", "ServiceTempLOB_ID = 35 AND ServiceTempStaffID = 123")

Typically the 123 would come from a form or someplace, so it would work with whoever was working on it. The link above should help with that.
 

&Boom

Registered User.
Local time
Yesterday, 19:39
Joined
Sep 16, 2013
Messages
21
Oh! I see now.


I tried this:

If DCount("*", "ServiceTempTbl", "ServiceTempLOB_ID = 35" And "ServiceTempStaffID = " & Forms!InputHoursFrm!StaffId) > 0 Then
MsgBox "Please correct all lines of business."


I am getting a "Type mismatch" error.
 
Last edited:

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 16:39
Joined
Aug 30, 2003
Messages
36,133
You didn't get the quotes in red from my version. ;)

If DCount("*", "ServiceTempTbl", "ServiceTempLOB_ID = 35" And "ServiceTempStaffID = " & Forms!InputHoursFrm!StaffId) > 0 Then
 

&Boom

Registered User.
Local time
Yesterday, 19:39
Joined
Sep 16, 2013
Messages
21
How foolish. You're brilliant.

You saved me from a very big headache. I want to thank you very much. I don't have much experience with Access, and have learned most of what I have learned from this forum and doing it myself, so thanks for being understanding.

Thanks again,
MC
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 16:39
Joined
Aug 30, 2003
Messages
36,133
No problemo!
 

Users who are viewing this thread

Top Bottom