Filling a Form when data does not already exist

[lubbs65]

So, i have a form that inputs data into a default table. One of the fields is "names". They select names via combo box. Let's say the user wants to enter the name "John Smith" which is not in the Combo Box (it doesn't already exist but needs to). Upon hitting Enter I want the message "The text you entered isn't an item in the list. Do you want to edit the item in the list?" to open.

I know the default tasks database allows this ability so I'm sure I can do it in my custom database. Any ideas?

 

[Trevor G]

You would use the Not In List Event behind your combo and set Limit to List to Yes

Here is a smaple code to do that.

Private Sub Company_NotInList(NewData As String, Response As Integer)
Dim lngresp As Long
Dim Db As Database
Dim rcd As Recordset
lngresp = MsgBox("Do you want to add this to the form?", vbYesNo + vbQuestion, "Add to List")
If lngresp = vbYes Then
Set Db = CurrentDb
Set rcd = Db.OpenRecordset("tblCompany")
rcd.AddNew
rcd![Company].Value = Me.Company.Text
rcd.Update
Response = acDataErrAdded
rcd.Close
Else
Response = acDataErrDisplay
End If
End Sub

 

[lubbs65]

You would use the Not In List Event behind your combo and set Limit to List to Yes

Here is a smaple code to do that.

that is almsot exactly what I want to do. There is however a slight change.

Private Sub Company_NotInList(NewData As String, Response As Integer)
Dim lngresp As Long
Dim Db As Database
Dim rcd As Recordset
lngresp = MsgBox("Do you want to add this to the form?", vbYesNo + vbQuestion, "Add to List")
    If lngresp = vbYes Then 
        Set Db = CurrentDb
        Set rcd = Db.OpenRecordset("tblCompany")
        rcd.AddNew
        rcd![Company].Value = Me.Company.Text
        rcd.Update
        Response = acDataErrAdded
        rcd.Close</font>
    Else
        Response = acDataErrDisplay
    End If
End Sub

Instead of what is on bold, I want to open up a form. How can I open a form in VBA? Then, upon opening the form according to my example they would enter the first and last age, and since they opened up the "name" form, maybe they would also fill out say that persons age, and phone number. They would not just be entering one field.

 

[Norie]

You should keep what's in bold but add the code to open the form after it.

That way you've created the record for the new name and you can open the form to that record so you can fill in the rest of the details.

To open a form you just use DoCmd.OpenForm.

With that you can specify the record to open the form at using the WhereCondition argument.

Exactly what you would use for that depends on how your data is structured.

You could use something like this:

"[Company]='" & NewData & "'"

The only problem there would be if the company field wasn't unique then more than one record would be returned.

 

[lubbs65]

Update:

So rather than using VBA as of right now, i used the macro builder. in "On Not in List" I set the action "open form" and it opens the form and they can input the data which is great!
then i have Stop Macro.

Here is the problem.
1)
Say "John Smith" is not in the list. I type in "Smith" (it searches by last name first) and nothing shows (ID # is still the key, not last names) So the form opens. Is there a way to have "Smith" by default go into the last name field?

2) I know there is a solution to this, but after putting in the contact information, I now want the name "Smith" to be in the original combo box right after entering it into the "Contacts" form. The name doesn't default shoot into the combo box. It still isn't even in the combo box field. I have to exit the form then re-open it to now see the name "John Smith" available.


How can I especially remedy problem 2 (if you can answer 1 too that would be great)?

 

[lubbs65]

Norie, im gonna look at what you posted as well (i originally hit refresh before posting but your post was not there, then it magically appeared when i made my "Update post").

 

[Norie]

Why would you want to use a macro?

The code Trevor posted can do the first part, and you can add what I mentioned at the end.

I realise the name might not be the best criteria to use for finding the newly created record that's why I mentioned the data structure.

Is the ID number an autonumber field?

If it is then we can use code to get it's value for the newly created record and use that in the where argument of OpenForm.

As for the item not appearing on the list that can easily be fixed with a quick refresh in the code.

 

[lubbs65]

here's my current code:

Private Sub Combo52_NotInList(NewData As String, Response As Integer)

    Dim strSQL As String
    Dim i As Integer
    Dim Msg As String
    Dim stDocName As String


    'Exit this sub if the combo box is cleared
    If NewData = "" Then Exit Sub

    Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
    Msg = Msg & "Do you want to add it?"

    i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Book Category...")
    If i = vbYes Then
    
    
        Set Db = CurrentDb
        Dim rcd As DAO.Recordset
        'Set rcd = Db.OpenRecordset("Contacts")
        'rcd.AddNew
        'rcd![Combo52].Value = Me.Combo52.Text
        'rcd.Update
        'Response = acDataErrAdded
        'rcd.Close
        'stDocName = "Contact Details"
        'DoCmd.OpenForm stDocName
        stDocName = "Contact Details"
        DoCmd.OpenForm stDocName
        
    Else
        Response = acDataErrContinue
    End If

End Sub

I still can't find out what exactly the original code that was supplied does that i have commented out.

Here are a plethora of issues that I am having.
1)all of the same issues exist that I had when I used the macro builder (we are now using the code builder)

2) i am still unsure of how to make this form open to a new field rather than one that already exists.

3)When I try this with the already given default form "Contacts" it opens up a form that kind of "Pops Out" so to speak. This window doesn't exist as a tab where all other forms,queries, and tables go when they're opened. This default "Contacts" form has multiple fields that are filled after realizing that the last name exists. A first name, and other information.

I can use the code above to open up other forms that open as tabs across the window If and only if they have one field that im entering. So lets say I had a table called "Stores" with only the field "Stores" in it. I would be able to open up this form.

When i try the same thing with say a "Recipes" combo box everything goes down hill. Let's say that the recipes Table combo box is based off of "cake" then another field that contains "ingredients" in the same table . I believe the fact that it has more than one field that will have to be entered I am getting an issue.

Edit:
ID number is an automatic field

I forgot to mention, the line in bold:
Set rcd = Db.OpenRecordset("Contacts")
has been giving me an error. I can not determine why yet.

How can I do a refresh in the code to then have the newly inputted data appear in the combo box right after?

 

[Norie]

You haven't really given us enough information to help with these issues.

Why are you opening 'Supplier Form' as a recordset?

Is it a table or query?


Also why you are just opening the form 'SORP Input Form' with no other arguments.

Like I said above you can open the form to a specific record using the WhereCondition argument.

I also mentioned that it there might be problems if the name wasn't unique and that if the ID was an autonumber code could be added to get the new record's ID.

As for the popup thing, that's probably nothing to do with the code it's more likely to fo with how the form being opened has been designed.

 

[lubbs65]

i made corrections from my original post because i noticed the text was wrong. please re-read it. i checked the forum like 20 min after my edit it still didn't display your post displayed omg (you were writing the post during my edits so my guess is didn't re-read because you would have no reason to).

can you elaborate on the problems in my code and how to use where conditions? i am terrible at using vba. i code in C.

Contact Details is a table to answer your question

also, i am aware that the line i say is wrong had a typo it should read:
Set rcd = Db.OpenRecordset("Contact Details")

i still however am getting an error.
adding "Dim rcd as recordset" above it doesn't help either.