Adding Records from Unbound Form to Database Table

[lzook88]

I am trying to write the unbound form to the database now using the same kind of loop.

Dim DBSS As Database
Dim rs As DAO.Recordset
Dim i As Integer

Set DBSS = CurrentDb
Set rs = CurrentDb.OpenRecordset("SELECT * FROM [Attendance]")



With rs
    .AddNew
        For i = 1 To 25
            !ADate = Me.Text0.Value
            !EXP_ID = Me.Controls("name" & i).Value 
            !Attended = Me.Controls("attend" & i).Value
        Next i
    .Update
End With

r.Close
Set rs = Nothing
DoCmd.Close


End Sub

and I am getting an error...

Run-time error '438':
Object doesn't support this property or method

when debugged it highlights:

 !EXP_ID = me.controls("name" & i).value

Thanks for your help!

[JeroenMioch]

Well im sure it wont solve your problem but if never seen an SQL statement where the table name is in brackets.
Try,

"Select ADate, EXP_ID, Attended from Attendance"

[ItsMe]

Add a debug.print line to your code so you can see which iteration of the loop the code is halting on.

For i = 1 To 25
Debug.Print i

What the error is saying is that your control named attendX does not have a .Value property. For instance, attendX may be a label or a command button.

[lzook88]

Name is a label. And I was attempting to pull the caption value from it. That would be the issue then. How would I go about doing that then?

[ItsMe]

While in design view of your form, if you highlight the label control by selecting it, you can view its members via the property sheet. Within the property sheet will be the available properties, as well as events. Alternatively, you can use intellisense while building code in the VBA editor. If you type the name of one of the labels and follow the name with a dot (Me.ControlName.), intellisense will display all of the available members. Of course, you have to type within a valid procedure in order for intellisense to function.

It sounds like you want the Caption property of the label. So something like ...

!Attended = Me.Controls("attend" & i).Caption

[June7]

Use Caption property for a label control. Only data controls have Value property and it is the default property which means don't have to type it.

You could have used textboxes instead of labels for the names.

Jeroen, [] are necessary if names of objects/controls/fields have spaces or special characters. They weren't needed in this case but don't hurt.

[lzook88]

Okay I am going to go through what you all posted one by one..

JeroenMioch - I updated the sql statement to include the EXP_ID, ADate, Attended to it.

ItsMe - I added the debug line, however it did not give me anything different.
I changed it from Value to Caption but as June7 posted I cant pull the value from a label

June7 - I converted all the label's to text boxes and updated the code to auto-fill them from the query.

My Code is now:

Private Sub CommandSubmit_Click()
Dim DBSS As Database
Dim rs As DAO.Recordset
Dim i As Integer

Set DBSS = CurrentDb
Set rs = CurrentDb.OpenRecordset("SELECT EXP_ID, ADate, Attended FROM Attendance")



With rs
    .AddNew
        For i = 1 To 25
        Debug.Print i
            !ADate = Me.Text0.Value
            !EXP_ID = Me.Controls("name" & i).Value
            !Attended = Me.Controls("attend" & i).Value
        Next i
    .Update
End With

r.Close
Set rs = Nothing
DoCmd.Close


End Sub

However, with the changes made above. When I press the submit button it gives me a debug error of:

Run-Time Error '3421':
Data type conversion error.

How I read this... is that it is possibly having a problem converting the full name on the text box back to the EXP_ID?

I have attached the database below..Attachment 22023

[ItsMe]

I might have been focusing on the wrong control name, believing the code was halting on ("attend" & i).Value.

As you mentioned in your first post, the exception occurs at the following line ..

!EXP_ID = Me.Controls("name" & i).Value

The issue is you cannot access a property of a control that does not exist. The code you posted in post #7 still has the .Value property. The issue is as simple as not using the .Value property for labels. Perhaps going through the two exercise described in post #5 will illustrate what properties are available for your controls.

[lzook88]

Correct.. Which I fixed in my most recent post.. I converted the labels I was trying to access to TextBoxes with the same name.

I might have been focusing on the wrong control name, believing the code was halting on ("attend" & i).Value.

As you mentioned in your first post, the exception occurs at the following line ..

!EXP_ID = Me.Controls("name" & i).Value

The issue is you cannot access a property of a control that does not exist. The code you posted in post #7 still has the .Value property. The issue is as simple as not using the .Value property for labels. Perhaps going through the two exercise described in post #5 will illustrate what properties are available for your controls.

[ItsMe]

I am not sure which line is causing the error. But yes, you need to assign values to the table that match the data types in the table. So if EXP_ID is a Number type, you need to do some data validation before trying to assign name5, name6, etc. to EXP_ID. When I use unbound controls I will use the Format property of the control to aid in data validation.

[lzook88]

ItsMe...

Please look at the above attached database (Post #7). I have Name1... etc showing as TEXT Fields.. however in the table they are stored as their ID's on the table I am trying to push this to. How would I go about changing them back to the IDs as they are recorded?

Thanks

[ItsMe]

I have to admit that I was scheptical whether iterating the control names was a good approach. I downloaded the DB and I think I see your objective and this may be the way to do it. I would still have to know more about the overall project to form an opinion.

That aside, you have the correct idea when you created the Attend table. You just diverted from the way I would manage the situation when you introduced the Value List with the combo. I would suggest using some SQL as the Row Source and Table/Query as the Row Source Type.

After making the change to the Row Source Type and Row Source, you will also need to make adjustments to the Row Widths, Column Count, etc.

It is best if you have an understanding of how to create a combo from scratch. Maybe some tutorial screencasts would be helpful. I created a combo series you can download here.
https://www.accessforums.net/tutoria...tml#post277671

If the tutorials are too long winded, you might be able to do some research on the properties online.
https://msdn.microsoft.com/EN-US/lib.../jj249312.aspx

[lzook88]

It isn't giving me issues with the combobox.. it is giving me an issue with their name on the attendance record. From what I read you are talking about the combo box on the form to say if they attended or not.

[ItsMe]

What I gather is you are using a form and comboboxes on a form to append records to a table. In this table, there is a Foreign Key of type number. You are getting an error here.

!Attended = Me.Controls("attend" & i).Value

I mention a combo because the combo is causing the data type mismatch. The Value of the combo is using data type text. If you change the RowSource of your combo to include the PK from your Attend table (AttendID), you can assign the AttendID to the FK of your Attendance table (the table you are trying to append that has the number field).

[June7]

If Exp_ID is the ID for explorer, then yes you need to save Exp_ID not explorer name. But first Exp_ID must be available. Load the Exp_ID to textboxes just as you have the names. Those textboxes can be not visible. Then your code must refer to those ID textboxes.