Ok Rob,
Let me preface this by saying I personally think it's probably not a good
idea to do it. Also, even if it is 'ok' to do it, there probably is a
better
way. However, I wanted to give you an example of how I accomplished it in
my
app that you MAY be able to use in yours.
One big caveat: I created a 'bad' record that isn't like any other
records
in my query behind the form (read: it's not a 'real' record...it's a
made-up
one). I created the record solely for the purpose of being able to run
code
when the user picks the record in the combo box. It is important to note
that having this 'bad record' is probably not a good idea. If I were
going
to do this in my application for real, I think I would AT LEAST add a new
'filetype' category (filetype is a joined table in my query and is
represented by a control on my form) such that I would have a filetype
category for JUST this file. That way I could exclude it when creating
reports. Otherwise, this 'bad record' would show up in my reports and I
just
wouldn't like that.
Ok, the code.
Basically, I added nested 'if' statements to the existing vba code I use
in
my combo box that users use to find records. It now looks like this and
works in my app:
__________________________________
Private Sub cmbQuickFile_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
On Error GoTo cmbQuickFile_AfterUpdate_Error
If Me.Dirty Then
If MsgBox("Save changes to current record before moving to new
record?", vbYesNo, _
"Save changes?") = vbYes Then
Me.Dirty = False
Else
Me.Undo
End If
End If
'Code added to make "New File" work
If Not IsNull(Me.cmbQuickFile.Text) Then
If Me.cmbQuickFile.Column(1) = "New File" Then
DoCmd.GoToRecord acDataForm, "Files", acNewRec
Else
'Stop 'New File Code
Set rs = Forms!Files.Recordset.Clone
rs.FindFirst "[ID] = " & Me![cmbQuickFile]
If Not rs.EOF Then Forms!Files.Bookmark = rs.Bookmark
'Code to end "Ifs" in "New File" code above
End If
End If
Exit_cmbQuickFile_AfterUpdate:
Exit Sub
cmbQuickFile_AfterUpdate_Error:
Call LogError(Err.Number, Err.Description, "cmbQuickFile_AfterUpdate",
Forms!Files.Name, , True)
End Sub
_______________________________
A few notes about the code and the file.
You will have to change the control name to match your form's combo box
control name.
My main form is called 'files'. You will need to change that to the name
of
your form.
The record I created in my app uses the term "New File" in the same field
that shows in the combo box that's used to find records. That means that
'New File' is one of the choices in my combo box. The user selects "New
File" in the combo box, and the form navigates to a new record.
The first part of the code isn't relevant to your question, but it
addresses
what happens if a user edits a record and then tries to use the combo box
before saving the record (my app requires users to save records manually).
I've marked the code I added with comments (prefaced by the single
quotation
mark).
The two lines below "If Me.Dirty Then" in the code should be on one line.
The last character of that line is the underscore character (_), which is
the
'continuation' character in vba which allows you to break up a long line
of
code into two lines but which will treat it like it is all one line. The
last part of the code also has two lines that split where they shouldn't
have. There is no continuation character in those lines because I didn't
split it up in my code in my app at all.
Basically, you just run separate code if they choose 'New File', and if
they
don't you run the 'normal' code for the combo box. In order to get the
'New
File' option, you have to create a record that has 'New File' in the field
that provides the values that display in your combo box used for searching
for records. In your case, I'm assuming that field would be "ModuleName",
though it sounds like you maybe didn't set your combo box up the way it
should have been.
Your combo box should have two columns: The primary key field is Module
ID,
and should NOT be visible. The second column should be Module Name, and
those values are what will appear in the combo box and what your users
will
choose from to navigate to a record.
From a more practical standpoint, and assuming the name of your
recordsource
for the form is named ModuleQuery, then you should select the combo box in
design view of the form (or draw a new one to start over), open the
properties window, and in the data tab, rowsource property type this:
Select [Module ID], [Module Name] from ModuleQuery Order by [Module Name];
The 'bound column' is set to 1. Over on the format tab, the 'column
count'
should be 2, and the 'column widths' should be: 0;1"
If you make the appropriate changes (change 'ModuleQuery' to whatever the
table or query is in your database, and make sure to use the proper
control
and form names, then this code should work.
As we speak, data managers across the globe are gathering to plot my
assassination for even suggesting a solution like this, but it DOES
accomplish your goal if I understand it correctly.
Sorry for the long post and the misunderstanding. I've just never heard
of
anyone using a combo box like that to add new records.
Good luck.
CW
Rob W said:
Ive tried to strip the system of its 'Access' look and style.
I've made my own navigation buttons and hidden the navigation bar.
So I want a combo box that will SEARCH record AND allow new values to be
edited.
Rob
