Easy ComboBox Question 3

[ssVBAdev]

Good Day All.

I am fairly new to database design and have a question about a combobox.

I have 2 tables, on is the "main" table and the other is a table of Cities (names of cities).

I built a Form based on the "main" table and have included a combobox on the form to read from the City table and add the value to the "Main" table.

I want to be able to have the users add a city name (already done by setting the "limit to list" property to No) but I want to program to add the new city back to the City table.

So, finally, My question is: How can I have a ComboBox on a form send a value to the "Main" table but also, if there is not already a matching City Name in the City table, add that value back to the City table.

Is there a built in function that can do this? Is it a matter of setting up a relationship? Do I build an SQL expression to handle it? Am I looking at VBA to accomlish this?

I'm just so full of questions this morning...

********************
What's the best way to get the answers you need?? See FAQ222-2244 for details!

 

[LarryDeLaruelle]

This is the code I use to add a single field value to the table underlying the combo box. It goes in the On Not In List event of the combo.

It adds the user entered item where that value is not in the list and will set the combo box to the entered item (and thus store in your main table).

Limit to List must be set to yes.

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

Dim dbTemp As Database
Dim rsTemp As Recordset
Dim strMsg As String

strMsg = "'" & NewData & "' is not currently in the list!" & vbCrLf & vbCrLf & _
"Do you wish to add it?"

If MsgBox(strMsg, vbYesNo + vbQuestion, "Your Message Header") = vbYes Then
DoCmd.RunCommand acCmdUndo
Set dbTemp = CurrentDb
Set rsTemp = dbTemp.OpenRecordset("tblName", dbOpenDynaset)
On Error Resume Next
With rsTemp
.AddNew
!FieldName = NewData
.Update
.Close
End With
Response = acDataErrAdded
Else
Response = acDataErrContinue
cboName = ""
cboName.SetFocus
End If

If Err Then
MsgBox "An error occurred" & vbCrLf & vbCrLf & "Please try again"
Response = acDataErrContinue
End If

Set dbTemp = Nothing

End Sub

Change cboName, tblName and FieldName to the appropriate values.

If you want to add more than one field (city & state) that requires a different approach. I have code for that also if you are interested.

HTH


Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[ssVBAdev]

Argh.... What references do I need to use that code. I tried using Microsoft DAO 3.6 and that did not work.

When the code fires, it breaks and tells me that the user defined type (dbTemp As Database) is not defined.

********************
What's the best way to get the answers you need?? See FAQ222-2244 for details!

 

[LarryDeLaruelle]

The dao 3.6 ref should do the trick all by itself.

If it ain't working try dimming the db and rs with the dao. prefix as:

Dim dbTemp As DAO.Database
Dim rsTemp as DAO.Recordset

That should do it.

Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[ssVBAdev]

Okay... That's working now. But, now I'm getting a Type Mismatch error at the line:
Set rsTemp = dbTemp.OpenRecordset("tblCity", dbOpenDynaset)
(tblCity is the name of the table I want to add the value to)

Any thoughts?

********************
What's the best way to get the answers you need?? See FAQ222-2244 for details!

 

[LarryDeLaruelle]

I'm guessing that you can get rid of the dbOpenDynaset; should not be necessary in any event.

Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[ssVBAdev]

Nope. Same error with:
Set rsTemp = dbTemp.OpenRecordset("tblCity")


********************
What's the best way to get the answers you need?? See FAQ222-2244 for details!

 

[LarryDeLaruelle]

Sorry for the delay; been out of the office.

May sound elementary, but make sure you are dimming the rsTemp as a Recordset.

I remember getting this message when I mistakenly dimmed rsTemp as a database.

If that's not it, post the entire code you're using so I can take a peek.

Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[ssVBAdev]

It should be pretty much the same code you gave to me. I commented the Error line to try to find out what's going on but other than that I did not change much...

Private Sub cboCity_NotInList(NewData As String, Response As Integer)
Dim dbTemp As DAO.Database
Dim rsTemp  As DAO.Recordset
Dim strMsg  As String

    strMsg = "'" & NewData & "' is not currently in the list!" & vbCrLf & vbCrLf & _
            "Do you wish to add it?"
    
    If MsgBox(strMsg, vbYesNo + vbQuestion, "Add City?") = vbYes Then
        DoCmd.RunCommand acCmdUndo
        Set dbTemp = CurrentDb
        Set rsTemp = dbTemp.OpenRecordset("tblCity")
        'On Error Resume Next
        With rsTemp
            .AddNew
            !FieldName = NewData
            .Update
            .Close
        End With
        Response = acDataErrAdded
    Else
        Response = acDataErrContinue
        cboCity = ""
        cboCity.SetFocus
    End If
    
    If Err Then
        MsgBox "An error occurred" & vbCrLf & vbCrLf & "Please try again"
        Response = acDataErrContinue
    End If
    
    Set dbTemp = Nothing
End Sub

********************
What's the best way to get the answers you need?? See FAQ222-2244 for details!

 

[LarryDeLaruelle]

Delete the DoCmd.RunCommand acCmdUndo line.

Don't forget to change 'FieldName' to the correct value.

Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[ssVBAdev]

Well, that was dumb. I did forget to change the 'FieldName'. That was exactly it. Works like a gem now. Thanks Larry. Have a

********************
What's the best way to get the answers you need?? See FAQ222-2244 for details!

 

[LarryDeLaruelle]

My pleasure and thanks for the star.

Many's the time I've pulled my hair out over something just as simple.

Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[monkeysee]

Larry De Laruelle

I have a similar problem that your other approach may help me that you refered to in this post "If you want to add more than one field (city & state) that requires a different approach. I have code for that also if you are interested"

What I have is a address combo box that is limited to list and have a "not in list" event tied to it also.

My problem is this: If the user is entering an address say for instance: 201 Main Street. This address IS in the data base but for a different city. If the user uses this address, then it really will not be a true value since it belongs to another location. How can I include the city as part of the not in list function for the street address?

Hope I am making sense. AND I am sure the answer is a simple one!

Thanks for your help, Deb

 

[LarryDeLaruelle]

Deb:

You have your users select street address from a combo box?

Does the underlying record source also contain the city/state information?

Are you saving the literal value displayed in the combo or the primary key of the address record?

Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[monkeysee]

Larry De Laruelle

Answers to your questions;

You have your users select street address from a combo box?
Yes, users are selecting street address from the combox box.

Does the underlying record source also contain the city/state information? Yes, the underlying source is the 'AddressLocation' table. It contains, the street address, city, state & zip

Are you saving the literal value displayed in the combo or the primary key of the address record?
Control source is: Septic.AddressLocationID
Row source is: SELECT [AddressLocation].[AddressLocationID], [AddressLocation].[Street] FROM AddressLocation;
And is bound to column 1 (primary key)

Thanks for looking at this!, Deb

 

[LarryDeLaruelle]

Deb:

I would suggest including the city and state info with the street address in the combo.

Change your row source to:

Row source is: SELECT [AddressLocation].[AddressLocationID], [AddressLocation].[Street] & ", " & [AddressLocation].[City] & ", " & [AddressLocation].[State] AS FullAddr FROM AddressLocation;

I used 'City' and 'State'; you'll need to change that to whatever the actual field names are.

This simply concatenates the three elements into a single column, but it will allow you users to see which city and state the street address is associated with.

Is this all you need or do you also need a mechanism to allow your users to add new street/city/state records on the fly?

Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[monkeysee]

Larry, Thanks! That worked Great! It would be great if my users could do this on the 'fly' as you suggest. What do I need to do to accomplish this? Thank you so much for being so helpful.

 

[LarryDeLaruelle]

Deb:

The method I use involves a function that that is called by the On Not In List event. This function will open the necessary form, allow entry of the new record and return a new record value.

If you would like it, I'd be happy to pass it on. It's a Word doc so the easiest way to get it to you would be by e-mail. Just respond to this post to my e-mail and I'll reply with that doc attached.

The beauty of using the function is that it can be called from any input form where you want the user to be able to enter new values on the fly.

I'll be out of the office tomorrow but I can send it to you first thing Friday.

Larry De Laruelle
ldelaruelle@familychildrenscenter.org