Search results

Hi plog Here is the example (and stripped down) db. No of records have changed but the same issue persists. I have created two queries, TrainingRecordQ (without Course_JT) and another one with Course_JT. As for the data I expect query to return is same as that in EmployeeXCourse_JT but with the...

Hi all In my training db, I have CourseListT and Course_JT tables. CourseListT contains CourseID (PK), CourseName, and ValidFor fields. Course_JT contains DeliverID (PK), CourseID (FK), ProviderID (FK), and other fields. This is to handle the situation where any course may be delivered by more...

Ahh, that solves it. It was actually the code Form_Delete event which was causing the error. I commented out the code and it works fine. Thank you, theDbguy!

Hi all For my training db, I have a CourseListT which contains CourseID and CourseName fields. I have another juntion table called Course_JT which has following fields: DeliverID (PK) CourseID (FK) ProviderID (FK) CertificationAuthorityID (FK) CourseCode TrainingLevel Cost One of the purposes...

@The_Doc_Man @Pat Hartman I dont know why I did not think of the orphan records issue. It would have been very simple to understand otherwise. Thank you for your inputs, as always

Hi CJ Thanks for the detailed response. The database will be used by various users and we will definitely be using filters. Seems like what I am trying to do is too much hassle for much less return. Think I might keep it the way Access by default works. Appreciate your response! :)

Hi Gasman Theoretically, it makes sense. But I am not sure how I would setup a query which will delete only the "selected" entries. Is there a way in query builder or VBA which can make the distinction between records which are selected and those which aren't?

Hi all In continuation of this thread, I am looking to have similar function where I can select multiple values and delete them at once, rather than deleting each value individually. My listbox is bound to a table, and as such row source type is set to table/query. Here is my code: Dim i As...

Yes that is right. What I meant is that the form_beforeupdate event triggers even if you move from MainF to any subforms, and vice versa. I was hoping that using the tmp table method, I could have the desired functionality. I understood this pretty early in my learning that unbound forms need...

Hi Doc The issue with using Form_BeforeUpdate event is that it triggers as soon as you move from MainF to any of the subforms, or vice versa, saving the data in the process. I completely agree about "a strategy that writes a record and then tries to remove it is inferior to a method that...

Hi all I have a main single form (MainF) which displays various types of records through use of continuous subforms (SubF1 and SubF2) for an employee chosen on MainF. To prevent accidental changes in records, I have put in an EditBtn which lets the user to change records. I have multiple...

Thank you everyone for your input. Always learn something new or expand my knowledge here. I will stick to form_beforeupdate, as suggested by pretty much everyone. Appreciate your time and effort :)

In response to another poster, I realised I was not clear with the description so I explained that the msgbox pops up if I enter the name and click outside that record i.e., I have to fill all the fields on a record before I move on to the next record, which is expected of form_beforeupdate.

I understand it is somewhat "unconventional" approach given how Access works. I was hoping there would be a way for a code to scan through all records in the underlying table and see if anything is not populated (). However, if it turns out to be impossible then I might have to change the approach

I like the idea of using select query to detect invalid record. I will give that a go. I am using a temporary table for the precise reason as to stop bad data from getting recorded in the permanent table. The process is only used to add rows. I have not come across transactions in Access...

Wouldn't this have the same issue i.e., if the user moved on to new record without having filled all three controls, they'd get an error message? Edit: If an employee fails the training, I can add another checkbox called Fail and create an append query to another table e.g., FailedT. But I...

This already happens, and I was kind of hoping it wouldn't happen. So for example, if I had to enter 20 employees, I want to check if any control on any record is empty only when I press SaveBtn.

It would be preferable to let the user know which controls are empty, which I believe can happen using setfocus property and highlighting the control with a colour, for example. But the main functionality I am struggling with is to actually get the code to detect empty controls

My bad. I used the form before update event. The msgbox pops up if I move away from employee name without clickin on pass or filling in Certification Date, but this may pose slight issue later down the line. That's why I am trying to validate all data when SaveBtn is clicked, before the data is...

I quickly put the following code in the before update event: Dim ctrl as Control For Each ctrl In Me.Controls If isnull(ctrl) Then msgbox ctrl.Name End If Next ctrl What happens is that this code generates the msg box as soon as I enter an employee in cboEmployeeID. For...