Need Combo Not in List event to open to a new record 3

[PamelaD]

From a newbie (using Access97)

Table source is [tblBuildings], which has three fields, [BuildingID], [BuildingName], and [BuildingDetails].
My data entry form is [frmBuildings] (control source is [tblBuildings]). On [frmBuildings], I have placed an unbound combo box [cboFindBuilding] to use to locate the records. The control source of the combo box is also tblBuildings. My key field is BuildingID; BuildingName is indexed, w/no duplicates.

I need my form to do are three things:

1) When I type into the combo box the name of a building whose name is not in the combo box/tblBuildings, I want the combo box to ask if I would like to add it.

2) Upon an affirmative answer,I want the form frmBuildings (which I already have open)to open to a new record, and put the data from the combo box into the field named BuildingName.

3) With the new record added, I would like the combo box to reflect the addition of the new record.

Hope this makes sense. I have hundreds of records in this table so it will make my life alot easier if I can rig this up.

Thanking you very much in advance. PamelaD

 

[jbehrne]

Hi PamelaD,

Try this:
**Change the names accordingly:
- ComboBoxName - change to the name of the combobox you wanted to add the item in if it's not in the list
-In the Dlookup statement change FormName to the name of the form housing the combobox
-In the DoCmd.OpenForm change FormName2 to the name of the form that you will enter the record information for the added building name
-Change this statement: Forms!FormName2.TextBoxBld, you must change the FormName2 to the above name and TextBoxBld must be changed to the reflect the field on that form that will hold the building name that is being passed to it
***********************************************************
Dim VarX, Style, Title, Response

VarX = DLookup("ComboBoxName", "tblBuildings", "ComboBoxName = Forms!FormName.ComboBoxName&quot

If VarX = Forms!FormName.ComboBoxName Then
Exit Sub
Else
Msg = "Do you want to add the item ?"
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define msgbox buttons.
Title = "Add Item?" ' Define msgbox title.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then ' User chose Yes.
DoCmd.OpenForm "FormName2", acNormal, , , acFormAdd
Forms!FormName2.TextBoxBld = Me!ComboBoxName
Else ' User chose No.
Me!ComboBoxName = ""
Me!ComboBoxName.SetFocus
End If
**********************************************************
When you close the form that you open to add the new building you will need to requery the form that you initially opened (I.E. - the form that the user originally opened and added the name in).

HTH,

jbehrne If at first you don't succeed, call in an airstrike. - Murphy's Laws of Combat Operations

 

[PamelaD]

Thank you very much for taking the time to respond to my query. Upon reading your suggestions, I think that I might not have made myself clear in my initial description, as your solution mentions FormName(1) and FormName(2).

If it is possible, I would like to do this using only one form, my data entry form [frmBuildings]. The combo box is on this form. If a user types in a building name that is not already in the table, I need the not in list event to ask if user wants to add this new entry, and then if the response is yes, I need [frmBuildings] to pop ahead to an empty record on itself, [frmBuidlings], with the text that was entered in the combo box appearing in the [BuildingName] field. Hope this makes sense...

Best regards, PamelaD.

 

[jbehrne]

Ahhh,

Yes that does make a difference! Try this code:
- make sure you change names as before!
Dim VarX, Style, Title, Response, NewBldName

VarX = DLookup("ComboBoxName", "tblBuildings", "ComboBoxName = Forms!FormName.ComboBoxName&quot

If VarX = Forms!FormName.ComboBoxName Then
Exit Sub
Else
Msg = "Do you want to add the item ?"
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define msgbox buttons.
Title = "Add Item?" ' Define msgbox title.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then ' User chose Yes.
NewBldName = Me!ComboBoxName 'Set vb variable to new building name
DoCmd.GoToRecord , , acNewRec 'create new record
Me!ComboBoxName = NewBldName 'set comboboxname to variable value
Else ' User chose No.
Me!ComboBoxName = ""
Me!ComboBoxName.SetFocus
End If
End If

Sorry about the confusion & I hope this helps!

jbehrne If at first you don't succeed, call in an airstrike. - Murphy's Laws of Combat Operations

 

[PamelaD]

When I said I was a newbie, I wasn't kidding. I cannot seem to make this thing work. I have put a copy of it here:

http://www.iserv.net/~plucas/access.

Any chance you could take a look and see what I'm doing wrong? Will be much indebted, and I sincerely thank you for all of your help to this point. PamelaD.

 

[jbehrne]

Hi PamelaD,

I have fixed your problem. Please post an e-mail address that I can send the new db to you (or you can e-mail me your address at: jbehrne@hotmail.com) I also need to know what version of access you are using so that I can send you the correct db version,

jbehrne If at first you don't succeed, call in an airstrike. - Murphy's Laws of Combat Operations

 

[jbehrne]

To anyone who has been following this thread I am posting the code that handles adding a new record in a form when a new value is entered.

Notes:
1. This example is running on a form that is using an unbound combobox to display names of buildings from a table. Once the user selects a building name the form is updated to display the building name and other record information. The combobox runs this query:

SELECT tblBuildingsNameQuery.BuildingName FROM tblBuildingsNameQuery ORDER BY BuildingName;

2. Furthermore the combobox is not limited to list - so you can add records.
3. If the name is not found in the table then display a msgbox to ask the user if they would like to enter the name as a new record. If user chooses yes the go to new record on the form and update with new name, requery and display the new record. If user chooses no then clear the combobox and set it's value to Null.


Here is the code:

Private Sub cboFindBuilding_AfterUpdate()
Dim VarX, VarBuildingID, Style, Title, Response, NewBldName

'Look up the building name and save the value to the variable
VarX = DLookup("BuildingName", "tblBuildings", "BuildingName = Forms!tblBuildings.cboFindBuilding&quot
'Look up the building id number
VarBuildingID = DLookup("BuildingID", "tblBuildings", "BuildingName = Forms!tblBuildings.cboFindBuilding&quot

If VarX = Forms!tblBuildings.cboFindBuilding Then 'If building name is found
'Requery and show the record
Me.RecordsetClone.FindFirst "[BuildingID] = " & VarBuildingID
Me.Bookmark = Me.RecordsetClone.Bookmark
Else 'User has entered a new building name, ask to save record
Msg = "Do you want to add the item ?" 'Msgbox Message
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define msgbox buttons.
Title = "Add Item?" ' Define msgbox title.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then ' User chose Yes.
NewBldName = Me!cboFindBuilding 'Set vb variable to new building name
DoCmd.GoToRecord , , acNewRec 'create new record
Me!cboFindBuilding = NewBldName 'set comboboxname to variable value
Forms!tblBuildings.BuildingName = NewBldName 'Set new bld name to entered name
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 'save record
'requery and go to record
Me.RecordsetClone.FindFirst "[BuildingID] = " & Me!BuildingID
Me.Bookmark = Me.RecordsetClone.Bookmark
Me.cboFindBuilding.Requery 'Requery the combobox and show all values
Else ' User chose No.
Me!cboFindBuilding = "" 'Set combobox to null & clear form
Me!cboFindBuilding.SetFocus
End If
End If
End Sub

jbehrne If at first you don't succeed, call in an airstrike. - Murphy's Laws of Combat Operations