Combo box to retrieve and add new records?

[kvest]

Hello,
I am a bit new at this and have been able to accomplish both of the following, but only using seperate boxes. I would like to combine this function into one box to make data entry easier and havnt been able to find the answer to the following:

I would like to use a Combo or ??? to search for a primary key value as the user types in the data (2 letters and 8 numbers). If found, when they tab out of the field, it populates the rest of the form so that record can be updated. That is easy enough...but can it be configured to create a new record (with the inputed data as the new primary key) when the record isnt found and the user tabs out of the box?

This would permit a user to simitaniously check to see if a record has already been partially entered and then continue with the data entry if it wasnt located. The primary key field name is FileNumber.

Thanks in advance.....

 

[willir]

You can look into the NotInList event procedure.

Question: What if somebody fat-fingered a typo when entering their info in the combo box?

 

[kvest]

I agree that could be a problem, but the need to insure only on record with that number outways that. There are several queries and reports generated off this data, and if a type occurs, it will be caught along the line and either corrected or deleted. As stated earlier, I am a bit new at this and am trying to keep this project moving along.

Is there another way of accomplishing the same process I need and still providing some protection from fat finger errors?

 

[kvest]

Ok...I found the NotinList command and some code that supposedly will do what I need. I now get an error that states "Compile Error - User defined type not defined" in regard to the Dim dbs As DA0.Database and DIM rst As DA0.Recordset lines....

I found a reference in an old post to an object library in Acess 2K that needed to be turned on. I did that and still get the same error. What do those lines mean?

'Set Limit To List property to Yes.

On Error GoTo ErrorHandler

Dim intResult As Integer
Dim strTitle As String
Dim intMsgDialog As Integer
Dim strMsg1 As String
Dim strMsg2 As String
Dim strMsg As String
Dim cbo As Access.ComboBox
Dim dbs As DA0.Database
Dim rst As DA0.Recordset
Dim strtable As String
Dim strEntry As String
Dim strFieldName As String

'The name of the lookup table strtable = "Incident Approval"

'The type of item to add to the table strEntry = "File Number"

'The field name in the lookup table in which the new entry is stored strFieldName - "File Number"

'The add-to combo box Set cbo = Me![cboIncident_Number]

'Display a message box asking whether the user wants to add 'a new entry.
strTitle = strEntry & "not in list"
intMsgDialog = vbYesNe + vbExclamation + vbDefaultButton1
strMsg1 = "Do you want to add "
strMsg2 = " as a new " & strEntry & " entry?"
intResult = MsbBox(strMsg, intMsgDialog, strTitle)

If intResult = vbNo Then
'Cancel adding the new entry to the lookup table.
intResponse = acDataErrContinue
cbo.Undo
Exit Sub
ElseIf intResult = vbYes Then
'Add a new record to the lookup table.
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strtable)
rst.AddNew
rst(strFieldName) = strNewData
rst.Update
rst.Close

'Continue without displaying default error message.
intResponse = adDataErrAdded

End If

ErrorHandlerExit:
Exit Sub

ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & _
Err.Description
Resume ErrorHandlerExit

 

[PHV]

You have to reference the Microsoft DAO Library:
when inVBE, menu Tools -> References...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[kvest]

I have no idea what you mean...in the code or in the post....? If it is the post it was Microsoft DAO 3.6 Object Library....

 

[PHV]

Have you referenced it ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[kvest]

I found another post and changed the reference to something else: Dim dbs As Database
Dim rst As Recordset

Got past that problem and a couple of types to the current problem...I now get an Error 13 type mismatch. What does that mean?

 

[PHV]

That does mean that you are mixing ADODB.Recordset with DAO.Recordset.
As you use the Database object I guess you want a DAO.Recordset.
So go back to the Dim rst As DAO.Recordset and check your references.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[kvest]

Thanks...I get an error 3265 now. I give up. I will give up on this idea and use two different boxes.

 

[PHV]

Are strTable and strFieldName populated ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244