Add checkbox

[daniels012]

I have a table that has data and each employee name across the top.
The data is Text fields and the checkboxes are yes/no fields
I have a form I use to enter job number and who was on the job and all the data.
So the form has data fields and all the employee checkboxes.
Is there a VBA way I can add a new employee without having to go back to the table insert the new employee then choose the field and enter it on the form?

Maybe hit a button, an input box pops up, I enter a name for the new employee and the check box appears on the form.
Also it would be nice to add a data (text) field also, so if we have something new we want to add.
Is this even possible?

Michael

[daniels012]

Found this to add a field:

Public Sub addFieldToTbl()

On Error GoTo ErrHandler

Dim db As Database
Dim tbl As TableDef
Dim fld As DAO.Field

Set db = CurrentDb()
Set tbl = db.TableDefs("tblDepartments")

Set fld = tbl.CreateField("Organization", dbText, 30)
tbl.Fields.Append fld
fld.Properties("Required").Value = True
fld.Properties("AllowZeroLength").Value = False

CleanUp:

Set fld = Nothing
Set tbl = Nothing
Set db = Nothing

Exit Sub

ErrHandler:

MsgBox "Error in addFieldToTbl( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
GoTo CleanUp

End Sub

How do I get it to make it a checkbox?
Michael

[CreganTur]

What you want is called a data entry form.

To create one, you will need to create a Form that is bound to your table and pull all of the fields you want to work with onto the form, like normal. The only difference between a data entry form and a regular form is the properties that are used. On your form's properties window, go to the Data tab. Look for the Data Entry property, and set the property to True or Yes. This makes the form so that it does not show records that currently exist in the table. Instead it will always show blank fields for you to enter data into.

For the code behind your submit button you can use this. It will move to a new record (show another blank record). This automatically saves the data you input to the table, while giving you the ability to enter another record if you want:
DoCmd.GoToRecord , , acNewRec

HTH:thumb

[CreganTur]

Found this to add a field:
The code you found shows how to create a new object on your form at runtime. Generally this is frowned upon, because it can have unexpected consequences.

[daniels012]

Oh I see!
So there is no way to add a field to the table?

Michael

[daniels012]

I have a form already that is bound to my table. I have all the data fields and checkboxes on the form. It works great. I just was wondering if there was a way to add a checkbox(employee) programatically (VBA) instead of going back to the table and inserting a new line for the new employee and making it yes/no.

Michael

[daniels012]

Here is the link I found to give me what I thought I was after:
http://www.access.qbuilt.com/html/vba.html

Michael

[CreganTur]

You can add unbound controls to a Form. Meaning you could add a checkbox to your Form and control it via VBA. It just isn't bound to any field in your Table.

[daniels012]

I see! I guess I will do it manually each time.
Thank You,
michael

[CreganTur]

I see! I guess I will do it manually each time.

I wonder if we're having a little miscommunication.

You do not have to create the checkbox by hand every time- you only need to create it once on your form. Once it's on your form, it will always be on your form.

The difference between bound and unbound objects is this: when the form opens, bound objects will be populated automatically with record data from the table they are bound to. Unbound object will pull no data from the table. They are not connected to the table at all.

Does that make sense?

[daniels012]

No, When i said this:

I see! I guess I will do it manually each time.
All I am saying here is that I have to add a new employee to the folder manually.

Once the checkbox is added, I know it will stick to the form.
I do want a checkbox bound to the table, therefore, from what you are saying, I do need to add this manually everytime I add a new employee.

If you like I can send a copy of the database and you can see what i am talking about.

As far as miscommunication, that is possible, I am really green when it comes to Access code.
Michael