I have a from that displays an employees info when selected from a list. The employee then updates text boxes as well as combo boxes to describe a type of project they are working on. I'm trying to add what the employee selects to the Project table. Here is my code so far...

Dim mydb As DAO.Database
Set mydb = CurrentDb()
mydb.Execute "INSERT INTO Project(Project_ID, Dept_ID, Status, [Start_Date], [End_Date], Hours, Priority, SSO_Num, Type, Frequency, Comments) VALUES ('" & Department.Column(1).Value & "', '" & Project.Column(1).Value & "', '" & Status.Value & "', '" & ProjectType.Value & "', '" & Priority.Value & "', '" & Hours.Value & "', '" & Frequency.Value & "', '" & Comments.Value & "', #" & Format(StartDate.Value, "mm/dd/yyyy") & "#, #" & Format(EndDate.Value, "mm/dd/yyyy") & "#)"
    Dim rsP As DAO.Recordset
    Set rsP = mydb.OpenRecordset("Project", dbOpenDynaset)
    rsP!Status = "Status of Project"
    Status.AddItem rsP!Status
    rsP.Bookmark = rsP.LastModified

This code only shows one field of the project table which is status. When I click the AddRecord button on my form I get compile error message. I'm new to coding in VB with Access so if someone could please help I would appreciate it. Thanks.

8 Years
Discussion Span
Last Post by bhicks11

Is it possible you are over complicating this? If you are trying to append to the form's data source you just set the value of each control and when you move off the form, it will be save to the underlying table. What controls are on the form and are they bound to fields or unbound?


I don't think I understand. I clicked off of the form but it still did not save to the table. The controls on the form are combo boxes. Each control is bound to a field within the table and I have multiple tables. The user wills select their name from a drop down box and then select the project they are working on as well as status, hours, etc. Then I would like it to be saved to the database and then the user can add another record or just exit the form. Any suggestions? Thanks.


What is the data source of the form? You mentioned multiple tables - are the tables all included in the data source of the form? What is the source of the combo box and how does it relate to the other controls on the form? Are you populating other controls from the combo box?


This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.