Appendquery conversion error

[BartBE]

Hello everyone,

This is my first post here. I'm from Belgium, the Dutch speaking part. Generally speaking, English is usually not a problem for me. My knowledge of English Access terminology is limited however, so please bear that in mind when I try to translate I should also add I'm familiar with the standard Access environment and SQL, but I have no knowledge of VBA.

Attached to this post, you'll find (a slimmed down version of) my database. I have tried to translate relevant field and table names to English.

On to my problem:

I want to be able to register the trainings that employees have attended. I use the table "WN_Trainings" to do so: one record per employee per training. Note that the same employee can follow the same training several times due to legal obligations, so the combination employee ID + training ID does not have to be unique in this table.
(The table "Trainings available" stores information on the trainings.)

Because multiple employees follow the same training at the same time, I want to be able to add them in one move.
For this, I have created the append query "HLP_Q_Add trainings". It should add records to the table "WN_Trainings". I use the form "Inputform for training" to control the query: the form gathers the information the query needs.

When I launch the append query, I get an error: conversion from the query to the table does not succeed.

I am unable to find the source of the problem. I have tried several things, including leaving out every field (one at a time) to try and locate the problem. I've also tried explicitly converting the query fields to the proper type. (As you will see in the query.)

Curious observations:
If I open the append query in design view and then run the form, I get to see what the query would add. It looks fine.
Also, I've made a variant of the append query using parameters. This also works fine. But... I want the form It makes selecting employees and trainings much easier.

Can anyone help?

 

[jzwp22]

Welcome to the forum!

The first issue I see is with the append query. Since all of the data needed for the append is already (or should be) on the form why would you need to select data from 4 tables (see area in red below)

INSERT INTO WN_Trainings ( [Employee ID], [Training ID], [Start date], [End date], [Certificate ok] )
SELECT DISTINCT WN_Personal.Rijksregisternummer, CLng(Forms![Inputform for training]![Keuzelijst beschikbare opleidingen]) AS [Opl Id van form], CDate(CLng(Forms![Inputform for training]!StartdatumINVOER)) AS [Startdt van from], CDate(CLng(Forms![Inputform for training]!EinddatumINVOER)) AS [Einddt van form], Forms![Inputform for training]![Attest ok] AS [Att Ok van form]
FROM WN_Personal, WN_Employments, [Trainings available], WN_TrainingsWHERE (((CLng(Forms![Inputform for training]![Keuzelijst beschikbare opleidingen]))=Forms![Inputform for training]![Keuzelijst beschikbare opleidingen]) And ((CDate(CLng(Forms![Inputform for training]!StartdatumINVOER)))=CDate(CLng(Forms![Inputform for training]!StartdatumINVOER))) And ((CDate(CLng(Forms![Inputform for training]!EinddatumINVOER)))=CDate(CLng(Forms![Inputform for training]!EinddatumINVOER))) And ((Forms![Inputform for training]![Attest ok])=Forms![Inputform for training]![Attest ok]) And ((WN_Personal.[Temp selection])=True));

What you need is the more basic version of the append query something similar to this:

INSERT INTO WN_Trainings ( [Employee ID], [Training ID], [Start date], [End date], [Certificate ok] )
Values (10000000000,10,#5/1/12#,#5/3/12#,-1)

However, since you want to do the append for several people, it might be better to use the recordset approach in code.

Also, instead of using a subform, I would recommend using a multiselect list box, so you can select those personnel who have completed the training.

Out of curiosity, why do you have a certificate field in both the trainings available table as well a the WN_Trainings table?

 

[BartBE]

Hello jzwp22,

Thank you for your suggestions.
I have:

• Removed all irrelevant tables from the query
• Inserted a multiselect list box in the form, named "Multiselect_Name" (Containing Last name and First name from WN_Personal)
• Adjusted the query accordingly. (I'm attaching the updated version of my database.)

I get a new (or the same?) conversion error, however.
I know I need to specify that one record needs to be made for every employee that is selected in the list box. I do not know how to make that happen, though.
Currently, Access intends to create only one record, regardless of how many employees I select. The first field of that one record, which should contain an employee ID number, remains empty.
This could explain the error, since employee ID is a mandatory field in the target table "WN_Trainings".

However, since you want to do the append for several people, it might be better to use the recordset approach in code.

I'm afraid I'm not familiar with this. Is this done in VBA?

Out of curiosity, why do you have a certificate field in both the trainings available table as well a the WN_Trainings table?

The field in "available trainings" registers whether the training comes with a certificate once completed.
The field in "WN_Trainings" registers whether we have received a copy of the certificate from the employee that followed the training. (Which is important for legal reasons.)

 

[jzwp22]

In the attached database, I have added the Visual Basic for Application (VBA) code to add the the indicated training for multiple employees selected from the multi-select list box.

One problem I did see with your form is that your form was bound to the WN_Trainings table, so when you ran your query Access tried to do that but also tried to add a record via the form. I made the form unbound to correct this issue.

Regarding the certificate check box on the form, checking it will apply to all employees selected from the multi-select list box. So for example, if you select 5 employee from the list and check the certificate box, all 5 employees' training records will reflect that they have the certificate. I'm not sure if that is what you really wanted.

 

[BartBE]

Thank you very much! Works like a charm. I understand this problem couldn't have been solved in the standard Access environment? I guess I need to start looking for a training myself

I really didn't expect to get my work done for me, which is why I'm reluctant to ask for your help one more time. But I am unable to implement your work into my original database.

Like I said, I originally posted a slimmed down version of my database (to prevent spreading confidential data), in which I translated some table and field names from Dutch to English.
So I have attempted to copy all the changes you have made into my original database. I have:

• Copied the VBA code (in the editor)
• Looked through the code and renamed everything to match the original Dutch names
• Set the event for the button: on click, execute VBA code (instead of run query)
• I also changed the name of the button from (freely translated) "Run query" to "AddTrainingsVBA" to avoid confusion in the future. And renamed everything in the code accordingly.

Yet I keep running into a compiling error.

I have once again stripped my original database of any excess (and real) information. But this time: no translations There is only one form left, so finding it shouldn't be a problem. The table that it needs to write to is "WN_opleidingen(gevolgd)".

Regarding the certificate check box on the form, checking it will apply to all employees selected from the multi-select list box. So for example, if you select 5 employee from the list and check the certificate box, all 5 employees' training records will reflect that they have the certificate. I'm not sure if that is what you really wanted.

That is indeed what the original form was intended to do. I admit it doesn't make perfect sense, because in reality, we don't always receive all certificates at once. To be honest, I just didn't know where to start to get a form that selects a variable amount of employees and allows/registers variation in other fields.

 

[jzwp22]

The reason you received the compile error was because to be able to use an ADODB recordset, you have to set a reference to the Microsoft ActiveX Data Objects library. This is done from the VBA window (Tools-->References). You would just find the library and check the check box. See the screen shot in the attached Word document.

With respect to the code there were a couple of other issues based on field/table names.

Shown in red below, I had to enclose your table name in square brackets [] since the name of the table contains special characters (parentheses).

Shown in blue below, I had to set the control name of the list box to your actual name rather than the one in your previously posted database.

Shown in green below, I had to change the field names to your actual field names. Field names with spaces or special characters had to be enclosed in square brackets.

The attachment contains the Word document with the VBA references and the revised database.

I understand this problem couldn't have been solved in the standard Access environment? I guess I need to start looking for a training myself

I could not think of a good way to do this with just queries. I basically taught myself VBA with the help of some books such as Access VBA for Dummies

To be honest, I just didn't know where to start to get a form that selects a variable amount of employees and allows/registers variation in other fields

About the only way I know of how to do this is to have a series of list (single select) or combo box controls with check boxes next to them, but this would get quite cumbersome. You might have ten pairs of combo/check box controls on the form but what happens if you have 50 people you have to do record training for? So I think your approach is best, but you may have to go back in and either manually remove the check or run an update query to remove the check for those that do not yet have the certificate.

Private Sub AddTrainingsVBA_Click()
On Error GoTo AddTrainingsVBA_Click_Err

'set up some variables
Dim frm As Form
Dim ctl As Control
Dim varItm As Variant
Dim intI As Integer
Dim lngloop As Long

'set up the connections for the recordsets

Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection



'set up the recordset
Dim myRSTraining As New ADODB.Recordset
myRSTraining.ActiveConnection = cnn1

'open the recordset based on the WN_opleidingen(gevolgd) table
myRSTraining.Open [COLOR="Red"]"[WN_opleidingen(gevolgd)]", [/COLOR], adOpenDynamic, adLockOptimistic

If IsNull(Me.StartdatumINVOER) Then
    MsgBox "Voer een startdatum in"
    Me.StartdatumINVOER.SetFocus
    Exit Sub
End If

If IsNull(Me.EinddatumINVOER) = True Then
    MsgBox "Voor een einddatum in"
    Me.EinddatumINVOER.SetFocus
    Exit Sub
End If

If IsNull(Me.Keuzelijst_beschikbare_opleidingen) = True Then
    MsgBox "Selecteer een training"
    Me.Keuzelijst_beschikbare_opleidingen.SetFocus
    Exit Sub
End If





'set the control to the employee list box
[COLOR="Blue"]Set ctl = Me.Keuzelijst134[/COLOR]
'check to make sure at least 1 employee has been selected otherwise return message

If ctl.ItemsSelected.Count > 0 Then
    'for each selected employee, add the training to the WN_opleidingen(gevolgd) table
    For lngloop = 0 To ctl.ItemsSelected.Count - 1
        With myRSTraining
            .AddNew
            [COLOR="seagreen"]!Rijksregisternummer[/COLOR] = ctl.ItemData(ctl.ItemsSelected(lngloop))
            [COLOR="seagreen"]![Opleiding Id] [/COLOR]= Me.Keuzelijst_beschikbare_opleidingen
           [COLOR="seagreen"] !Startdatum [/COLOR]= Me.StartdatumINVOER
           [COLOR="seagreen"] !Einddatum[/COLOR]= Me.EinddatumINVOER
            [COLOR="SeaGreen"]![Attest ok][/COLOR] = Me.Attest_ok
            .Update
        End With
    Next lngloop
    MsgBox "Opleiding succesvol toegevoegd!"
    [COLOR="blue"]Me.Keuzelijst134 = Null[/COLOR]    Me.StartdatumINVOER = Null
    Me.EinddatumINVOER = Null
    Me.Keuzelijst_beschikbare_opleidingen = Null
    Me.Attest_ok = False
    
    
Else
    'No rows have been selected
    MsgBox "Er zijn geen medewerkers geselecteerd"
End If

myRSTraining.Close
Set myRSTraining = Nothing


AddTrainingsVBA_Click_Exit:
    Exit Sub

AddTrainingsVBA_Click_Err:
    MsgBox Error$
    Resume AddTrainingsVBA_Click_Exit

End Sub

 

[BartBE]

Jzwp22,

It works now. Thank very much for all your help!

I am going to look into learning about VBA. It looks interesting.

EDIT:

One problem I did see with your form is that your form was bound to the WN_Trainings table, so when you ran your query Access tried to do that but also tried to add a record via the form. I made the form unbound to correct this issue.

It just occurred to me I never looked into this. Are you referring to the record source property of the form (translated)? That is indeed empty now.

 

[jzwp22]

Are you referring to the record source property of the form (translated)?

Yes, that is what I was referring to. I removed the record source property thus making the form unbound.