Filling fields

[Guest]

I have a form used for tracking rep #s and names. Right now I have a form with a combobox that when you type the rep # automatically displays the corresponding name on the next field

This works great, however, is there a way so that rather than only selecting from an existing rep # from rep table, I can either choose an existing rep # or add a new one to rep table as I use my form

Rep table has two fields rep # and rep name

Thank you

Anna

 

[Jim Allensworth]

I have a form used for tracking rep #s and names. Right now I have a form with a combobox that when you type the rep # automatically displays the corresponding name on the next field.

This works great, however, is there a way so that rather than only selecting from an existing rep # from rep table, I can either choose an existing rep # or add a new one to rep table as I use my form?

Rep table has two fields rep # and rep name.

Set the combo box's Limit To List property to Yes and then code the
NotInList event to insert new reps. However that might be a little
problematic since you are not entering a name but selecting a number.
It could be overcome by using an input box.

- Jim

 

[fredg]

I have a form used for tracking rep #s and names. Right now I have a
form with a combobox that when you type the rep # automatically displays
the corresponding name on the next field.

This works great, however, is there a way so that rather than only
selecting from an existing rep # from rep table, I can either choose an
existing rep # or add a new one to rep table as I use my form?

Rep table has two fields rep # and rep name.

Thank you,

Anna

Anna,
There are several ways to do this
If the Rep table has an AutoNumber field you could append the new rep
name to the table and the Rep Number field will increment.

Here is one method....
In the ComboBox NotInList event:

' Prompt user to verify they wish to add new value.
If MsgBox("Product is not in list. Add it?", vbOKCancel) = vbOK Then
' Set Response argument to indicate that data is being added.
Response = acDataErrAdded
Dim strSQL As String
strSQL = " INSERT INTO tblCombo(ComboName) SELECT " & Chr(34) &
NewData & Chr(34) & ";"
CurrentDb.Execute strSQL, dbFailOnError
Else
' If user chooses Cancel, suppress the error message
Response = acDataErrContinue
Me!ComboBoxName = Null
End If

 

[Guest]

Fred,
The Rep Table does not have an autonumber field. These numbers are preassigned to the reps so it can't be automatic.

I tried your method and although I was able to input a new number and new name, nothing was added to Rep Table.

Please help me. Thanks.

 

[Guest]

Jim,

What do you mean by an input box? Can you be more specific?

Thank you, Anna

 

[Jim Allensworth]

Jim,

What do you mean by an input box? Can you be more specific?

Anna,

The InputBox function stops execution of code and throws up a box for
the user to input some information (as a string). Like a MsgBox except
that it take an input from the user.

You can try this in the debug window

InputBox ("Enter New Rep's Name","New Rep")

How are you getting the new number for the rep? It seem more intuitive
to me for the user to be selecting a name instead of number. If you do
it that way then the application can supply the number automatically -
and it is easier to code.

-------------word wrap alert-----------------------------------------

Private Sub cboRep_NotInList(NewData As String, _
Response As Integer)
Dim lngRepNum As Long

If MsgBox("Add " & NewData & " as a new Rep?", _
vbCritical + vbYesNo + vbDefaultButton2, _
"Confirm New Rep") = vbNo Then
Response = acDataErrContinue
Else
lngRepNum = DMax("RepNum", "tblReps") + 1
CurrentDb.Execute "INSERT INTO tblReps " _
& "(RepNum, RepName) VALUES (" & lngRepNum _
& ", " & "'" & NewData & "');", dbFailOnError
Response = acDataErrAdded
End If

End Sub

- Jim

 

[fredg]

Fred,
The Rep Table does not have an autonumber field. These numbers are
preassigned to the reps so it can't be automatic.

I tried your method and although I was able to input a new number and
new name, nothing was added to Rep Table.

Please help me. Thanks.

Add a couple of lines to the code I gave you to add the new rep name and
also add a user assigned RepNumber.
(Note: you also need to change my generic table and field names to
whatever your actual table and field names are!!!)

Dim strSQL As String
Dim intRepNum as Ingteger
intRepNum = InputBox("This new Rep's ID Number is")

strSQL = "INSERT INTO YourRepTable ( RepName, RepNum ) SELECT " &
Chr(34) & NewData & Chr(34) & ", " & intRepNum & ";"

When you click OK to add the new Rep Name, an Input Box will appear and
ask for the entry of the new rep's ID number.
Enter the new number, click OK, and the new rep's name and ID will be
added to the rep table.