asked on

Adding record to a table from data entry in another unbound form

Someone helped me a while back with an issue where I was trying to add a record to an unbound table from a field data entry in a form. Here's my code but it's broken and I can't figure out why.

When I try to close the form I get an error..."Too few parameters. Expected 1"

Here's my code...

Private Sub Form_Close()

'Code to create a new record in tblExample with values from combobox if that combination of data doesn't already exist in the table
If Me.cboProjectName > "" Then
If DCount("*", "tblProjectNames", "ProjectName = " & Chr(34) & Me.cboProjectName & Chr(34)) > 0 Then
'MsgBox "This record already exists."
Else
MsgBox "This appears to be a new record which does not already exist in the program. A new record is being created."
Dim RS As DAO.Recordset
Set RS = CurrentDb.OpenRecordset("tblProjectNames")
RS.AddNew
RS!ProjectName = Me.cboProjectName
RS.Update
End If
End If
'End of code to create a new record in separate table with values from combobox if that record doesn't already exist in the table

End Sub

SteveL13

ASKER

Actually, here is what I am trying to accomplish. I want the user to be able to type a value in a combobox and then be able to select that value for future records. I guess I don't care if that value is entered into the unbound table.

What is the best method to accomplish this?

Scott McDaniel (EE MVE )

then be able to select that value for future records.

So you want to select a single value, and then use that value for other things (like searches, or something like that)?

If so, you can use a variable declared at the proper "scope". If this is all done on a Form, then just declare your variable in the General Declarations section of that Form's code module:

Public gMyVar As String

Then you can set that value as needed:

gMyVar = "Bob"

If you need greater scope, then you can use the hidden form technique, which has several advantages. To do that, create a form that contains several textboxs, and open that form when the database first opens (you can open it as Hidden to hide it from view). Once that's done, you can then set your values into those textboxes:

Forms("YourHiddenForm").SomeTextbox = "Bob"

To refer to that value from anywhere in your app, you'd do this:

Msgbox "The current user is " & Forms("YourHiddenForm").SomeTextbox

SteveL13

ASKER

No, I want to be able to either select an existing value OR type in a new value. If I type a new value I want to add that value to the unbound table but my code is broken.

ASKER CERTIFIED SOLUTION

Scott McDaniel (EE MVE )

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SteveL13

ASKER

This is crazy! For some reason the following code which I think is the last suggestion, is creating TWO records in tblProjectNames. One which is correct or what I would expect, but the 2nd record being created has the key number of the drop-down in the ProjectName field.

????????

Code:

Private Sub Form_Close()

'Code to create a new record in tblExample with values from combobox if that combination of data doesn't already exist in the table
If Me.cboProjectName > "" Then
If DCount("*", "tblProjectNames", "ProjectName = " & Chr(34) & Me.cboProjectName & Chr(34)) > 0 Then
'MsgBox "This record already exists."
Else
MsgBox "This appears to be a new record which does not already exist in the program. A new record is being created."
CurrentProject.Connection.Execute "INSERT INTO tblProjectNames(ProjectName) VALUES('" & Me.cboProjectName & "')"
End If
End If
'End of code to create a new record in separate table with values from combobox if that record doesn't already exist in the table

End Sub