NewB database question...

[saladbarkid]

I have an Access database that's an address book with a lookup (dropdown box). When a letter is typed in, it goes right to the last names that start with that letter.

If the correct name is found and clicked on, all the info is displayed in the corresponding text boxes.

What I would like to know is - when I enter a last name that is NOT in the list, I want to be able to push 'enter' and have the last name drop from the lookup box to the last name text box and create a new file in the list with that last name. I don't want to use an 'add new record' button either.

If a better explanation is needed, I will post again.

I don't know SQL so this is kinda tricky for me...

Thanks for the help.

[qball]

When a letter is typed in, it goes right to the last names that start with that letter.

sounds like a ddlb, (drop down list box, using terms freely).

If the correct name is found and clicked on, all the info is displayed in the corresponding text boxes.

Bingo, matched up!?!? How does that work?

Should be easy from there? editable ddlb?

[saladbarkid]

What I'd like is when the name is found to be "not_in_list" push 'enter' and that acts like an 'add new record' button. I tried to add code like that, but it didn't exactly like it. Code to do this would be REALLY helpful.




Thanks in advance.

[qball]

Code to do this would be REALLY helpful.

Of course it would, but, that's your job, for lack of better word. Thanks for providing no code, yourself.

"not_in_list" push 'enter' and that acts like an 'add new record' button.

lol, "push 'enter'", lol. Like the 'press any key'?

The answer is simple:

YOU populate drop down list box. right? How is this done? This is important btw.
Upon user choosing, YOU determine action (display record data). right?
What event occurs upon user choosing? What mechanism displays data?

[simple]
When YOU populate ddlb, add entry/value named "New Record", or whatever you like.
When user chooses "New Record", instead of displaying existing data, YOU display...whatever you need.
[/simple]

[Icculus]

What do you mean by "didn't exactly like it"?

[saladbarkid]

Well, this is kind of another programmer's code. I don't really understand it myself. What I tried to do was create a private sub look_up_box not_in_list () like that and have it execute an add new record action when the last name is not in the list. Like this:

Private Sub Command48_Click()
On Error GoTo Err_Command48_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_Command48_Click:
Exit Sub

Err_Command48_Click:
MsgBox Err.Description
Resume Exit_Command48_Click

End Sub

This didn't work exactly correct, what am I doing wrong?

[AltatemTC]

Is the combo box bound or unbound?

The way I usually handle this is to do the following
1.) Set the "Limit To List" property to Yes.
2.) Using the OnNotInList event to any manipulation.

If you could tell me a little more about what is going on, it would be helpful.

[saladbarkid]

The list box is unbound.

Limit to list was set to 'Yes'.

For the OnNotInList event, how do I get the name that's not in the list to be added to the end of the line?

Here's the code for the program - hope it helps.
Thanks for all the help so far.


Option Compare Database
Option Explicit

Private Sub Command28_Click()
On Error GoTo Err_Command28_Click


DoCmd.Close

Exit_Command28_Click:
Exit Sub

Err_Command28_Click:
MsgBox Err.Description
Resume Exit_Command28_Click

End Sub


Private Sub ENTRYDATE_AfterUpdate()
ENTRYDATE = [ENTRYDATE]
End Sub
Private Sub DoAddNew_Click()
On Error GoTo Err_DoAddNew_Click


DoCmd.GoToRecord , , acNewRec
Me.LASTNAME.SetFocus

Exit_DoAddNew_Click:
Exit Sub

Err_DoAddNew_Click:
MsgBox Err.Description
Resume Exit_DoAddNew_Click

End Sub

Private Sub LookupName_AfterUpdate()

' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[idnumb] = " & Me![LookupName]
Me.Bookmark = Me.RecordsetClone.Bookmark
If Me.RecordsetClone.NoMatch Then
Me.LASTNAME = [LookupName]
End If
Me.LASTNAME.SetFocus
Me.LookupName = Null


End Sub

Private Sub LookupName_GotFocus()
LookupName.Dropdown

End Sub
Private Sub Command48_Click()
On Error GoTo Err_Command48_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_Command48_Click:
Exit Sub

Err_Command48_Click:
MsgBox Err.Description
Resume Exit_Command48_Click

End Sub

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

End Sub

[AltatemTC]

I have used different names and added some notation. The combo box I used was name 'cboLastName' and the textbox 'txtLastName'. I abandoned trying to do things like this a long time ago due to some of the inefficiencies. I now use VB which doesn't have all the events that make work in Access cumbersome. If you still want to use Access I would put a lookup form together just to find the name and then limit the Access form to that name only, if it's a new one then you can open the Access form to a new record. This is a complete change in methodology for most folks.

I developed in Access for several years. I still have a copy of version 1.0. You will need to switch to VB or C++ if you want to emulate some of the things that major software vendors do because Access makes forms, data, and data integrity all one big ball. In VB, I use all unbound forms so that I can do whatever I want without being encumbered by the underlying data.

If you are going to do a lot of Access development, I recommend 2 things.

1.) Get a subscription to Access, VB Advisor.
2.) Get a copy of one of Ken Getz's books on Access, it doesn't matter whether it's 97 or 2000, the core concepts are the same and I have Ken Getz code and methodology throughout my applications.

I hope this helps.

Option Compare Database
Option Explicit
Private Sub cboLastName_AfterUpdate()

'-- Requery the form so the record added in the NotInList event is available
'-- This is grossly inefficient but is necessary with the current methodology
Me.Requery

'-- Find the first record with the appropriate ID
Me.RecordsetClone.FindFirst "[zlngAddressID] = " & Me.cboLastName

'-- If we find the record then set the bookmark
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

'-- Set focus to the last name
Me.txtLastName.SetFocus

'-- Clear the combobox value
Me.cboLastName = Null

End Sub
Private Sub cboLastName_NotInList(NewData As String, Response As Integer)

'-- Set the SQL to insert the new value into the table
Dim strSQL As String
strSQL = "INSERT INTO tblAddress (strLastName) VALUES (" & vbDQ & NewData & vbDQ & ")"

'-- Setup the database
'-- I added a project reference to Microsoft DAO 3.6 library
Dim db As DAO.Database
Set db = CurrentDb()

'-- Run the SQL to add the new record
db.Execute strSQL

'-- Set the response, if you don't the combo box will still bark at you
Response = acDataErrAdded

'-- Destroy the database object
Set db = Nothing

End Sub
'-- I use this to return a double quote when writing SQL strings and the like
Private Function vbDQ() As String
vbDQ = Chr(34)
End Function