Combo Box Creates New Record

[sznsx]

Hello All !

I am currently using a combo box to find records based on a list of dates (which is primary key) of a table.

What I need: When I type a value into the combo box that is not on the list, I want to be prompted with a message like "This record does not exist, would you like to create it?" .... If so, a new record is created with the date field (primary key) filled with the value that was typed.

Thanks !

 

[Carnafex]

Inside the combo box is an event called On NotInList. You'll need to write some VB code here to get what you want to work. Try what I write below, that should help you. Note that the LimitToList property must be set to true for the above event to occur.
(Also note that if limittolist is true, then a default message will be displayed notifying the user that the item isnt in the list, but the code below overrides that for your own message to appear)

Private Sub ComboBox_NotInList(NewData As String, Response As Integer)
    Dim bytResponse as byte
   
    bytResponse = MsgBox( "This record does not exist, would you like to create it?" , vbYesNo + vbExclamation, "Not In List")

    if bytResponse = vbYes then
        Response = acDataErrAdded
        docmd.GoToRecord , , acNewRec
        datefield = newdata
    elseif bytResponse = vbNo then
        Response = acDataErrContinue 'Show No message
    end if

End Sub

That should work okay. Its a little bit complicated, but allows you to change the message box when a value isnt found.

Ta

Jason

 

[sznsx]

More problems !

Hey, I tried your code and these are my results....

When I type a value not currently in the list, the MsgBox comes up with the text "The record does not exist, would you like to create."

If I click yes, the MsgBox runs in and endless loop, just keeps poping up.

If I click no, the MsgBox closes...

If I click yes and then in one of the looped MsgBox's I click yes, I get the error "Runtime error '2105' You can't go to the specific record", with the options to click END or DEBUG. If I debug, it goes to the coding area and the line "DoCmd.GoToRecord , , acNewRec" is highlighted in yellow.

---------------
I am not new to coding, however I am knew to access and database design. I know some VB so I am trying to debug this.

 

[sznsx]

Anything else?

I cant figure it out !

 

[WayneRyan]

sznsx,

Can you post what you have here? It's hard to visualize what is going
on. Compact/Repair, then ZIP, then attach.

Wayne

 

[sznsx]

Here's a sample of what I want

I made a sample DB about my topic. If anything else is unclear please let me know, thanks so much for taking a look!

 

[WayneRyan]

sz,

Experiment with it.

Wayne

 

[sznsx]

Excellent !

DoCmd.RunSQL "Insert Into Table1 (TheDate, First, Second) " & _
      "Values (#" & Me.Combo0 & "#, 0, 0)"
      Me.Combo0.Requery

I have been playing around with the DB you posted, I kind get of the idea of how it works... I just have a question about this line in your code. I am trying to incorporate your code in my real DB and I noticed that in your SQL statement you wrote (TheDate, First, Second) which is every field in my sample DB, and then the next line you gave values to those fields. If I wanted to use this in my DB, I do not have to write out every field now do I?

I am sorry for all the newbie questions, you must get so many like these and it has to get annoying after a while! I was reading a few other post's on this forum and heard a lot about how the MS Access help file has been getting worse and worse. Unfortunately I am using MS Access 2003 so I am pretty sure this is the worst help file. Anyway, If it is possible could someone please give a breif line by line description of what this code does?

Private Sub Combo0_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
If DCount("[TheDate]", "Table1", "[TheDate] = #" & Me.Combo0 & "#") = 0 Then
   If MsgBox("This record does not exist, would you like to create it?", vbYesNo + vbExclamation, "Not In List") = vbYes Then
      DoCmd.RunSQL "Insert Into Table1 (TheDate, First, Second) " & _
                   "Values (#" & Me.Combo0 & "#, 0, 0)"
      Me.Combo0.Requery
   Else
      Exit Sub
   End If
End If
Me.Requery
Set rs = Me.Recordset.Clone
rs.FindFirst "[TheDate] = #" & Format(Me![Combo0], "mm\/dd\/yyyy") & "#"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

-sz

 

[WayneRyan]

sz,

The following inserts ONE row into your database. In this case,
the table only has three fields and those are referenced. The "#"
are delimiters that tell SQL that it is a date field. There is no
"punctuation" required for numbers. Strings use a single-quote as
a delimiter.

This code will work, even if there are Third & Fourth numbers.
They are not referenced in the Insert statement, and their values
would be Null after the Insert.

The code would fail if TheDate was a Primary Key (and currently
existed in the data). It would also fail if a field such as
Third was not referenced, but was mandatory in the table definition
(All zero-length = No).

If the statement fails, you will not receive a warning!!!!

DoCmd.RunSQL "Insert Into Table1 (TheDate, First, Second) " & _
      "Values (#" & Me.Combo0 & "#, 0, 0)"
      Me.Combo0.Requery

Get your code in Design View,
Tools --> References
Then check Microsoft DAO, and set its priority higher that ADO.

' 
' Declare an Access DAO database
' Declare an Access DAO Recordset  (The Dim As Object is not
'                                   specific, should be avoided)
'
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
'
' If The Combo's value is in the table Then
'    If They want to create it
'       Create the Record (DoCmd.RUNSQL Insert ...)
'    Else
'      Exit Sub
'    End If
' End If
'
' Finally, find the newly created record and display it ...
'
If DCount("[TheDate]", "Table1", "[TheDate] = #" & Me.Combo0 & "#") = 0 Then
   If MsgBox("This record does not exist, would you like to create it?", vbYesNo + vbExclamation, "Not In List") = vbYes Then
      DoCmd.RunSQL "Insert Into Table1 (TheDate, First, Second) " & _
                   "Values (#" & Me.Combo0 & "#, 0, 0)"
      Me.Combo0.Requery
   Else
      Exit Sub
   End If
End If
Me.Requery
Set rs = Me.Recordset.Clone
rs.FindFirst "[TheDate] = #" & Format(Me![Combo0], "mm\/dd\/yyyy") & "#"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Wayne