CombBox to SEARCH and ADD records.

[Rob W]

Greetings,

To clear up my previous post (much confusement in them!) I have a form where
all navigation buttons have been created manually (hidden navigation bar).

I want a combo box to allow user to both SEARCH and ADD new values.

I can SEARCH with a combo box after using the button wizard (search records
option) but cannot change its properties to add new values to the list.

Can this be done with form/vba code?

Thanks
Rob

 

[Rick Brandt]

Greetings,

To clear up my previous post (much confusement in them!) I have a
form where all navigation buttons have been created manually (hidden
navigation bar).
I want a combo box to allow user to both SEARCH and ADD new values.

I can SEARCH with a combo box after using the button wizard (search
records option) but cannot change its properties to add new values to
the list.
Can this be done with form/vba code?

You can alter the RowSource of the ComboBox to make it a UNION query. The
second part of the UNION can add the words "Add New" and you can make it display
in the first row. Then in the AfterUpdate event code that already exists on
your ComboBox you can test for the value "Add New" and have that take the form
to the new reocrd position.

 

[Rob W]

Wow. Thanks a simple, yet clever idea.

However I need some guide with my union statement :-

SELECT tblModule.ModuleId,tblModule.ModuleName, tblModule.Archive
FROM tblModule
UNION
SELECT tblModule.ModuleId, ModuleName as "Add New Record", tblModule.Archive
FROM tblModule;

It displays the modules names (columns widtsh 0;5;0 though bound to
module.ID) and not "Addd new Record"

I guess I should be concearned that there is macro code behind the
AfterUpdate combo box event and it will run the macro and the vba code?

Cheers
Rob

 

[Rick Brandt]

Wow. Thanks a simple, yet clever idea.

However I need some guide with my union statement :-

SELECT tblModule.ModuleId,tblModule.ModuleName, tblModule.Archive
FROM tblModule
UNION
SELECT tblModule.ModuleId, ModuleName as "Add New Record",
tblModule.Archive FROM tblModule;

SELECT tblModule.ModuleId,tblModule.ModuleName, tblModule.Archive
FROM tblModule
UNION
SELECT -9999 as ModuleId, "Add New Record" as ModuleName, null as Archive
tblModule.Archive FROM tblModule;
ORDER BY ModuleId

It displays the modules names (columns widtsh 0;5;0 though bound to
module.ID) and not "Addd new Record"

I guess I should be concearned that there is macro code behind the
AfterUpdate combo box event and it will run the macro and the vba
code?

Ugh. I would replace the current macro with vba code that does both things. Is
this Access 2007? Any other version and the wizard would have created VBA code
for you in the first place.

 

[Rob W]

Its Access 2007.

Ive doen the combo box without the button wizard now, playign aroudn with
this code:-

Private Sub cboModulename_AfterUpdate()
If cboModuleName.Value <> "NEW" Then

' Find the record that matches the control.

Set rs = Me.Recordset.Clone

rs.FindFirst "[ModuleId] = '" & Me![cboModuleName] & "'"

If Not rs.EOF Then Me.Bookmark = rs.Bookmark

ElseIf cboModuleName.Value = "NEW" Then

If rs.AbsloutePosition = rs.RecordCount - 1 Then

DoCmd.GoToRecord , , acNewRec

End If


End If

End Sub

I set the moduleId to be "NEW" in the union query. I s these ao reason for
the -9999 value (I forgot to mention module ID is of type text - 8 chars)

Rob

 

[Rick Brandt]

Its Access 2007.

Ive doen the combo box without the button wizard now, playign aroudn
with this code:-

Private Sub cboModulename_AfterUpdate()
If cboModuleName.Value <> "NEW" Then

' Find the record that matches the control.

Set rs = Me.Recordset.Clone

rs.FindFirst "[ModuleId] = '" & Me![cboModuleName] & "'"

If Not rs.EOF Then Me.Bookmark = rs.Bookmark

ElseIf cboModuleName.Value = "NEW" Then

If rs.AbsloutePosition = rs.RecordCount - 1 Then

DoCmd.GoToRecord , , acNewRec

End If


End If

End Sub

I set the moduleId to be "NEW" in the union query. I s these ao
reason for the -9999 value (I forgot to mention module ID is of type
text - 8 chars)

I dont understand why you are testing for the rs.AbsloutePosition in the ElseIf
block. If the user selects "Add New" then that is what they want regardless of
where they are currently positioned (correct?). I think you DO need to test to
see if they are already at the New Record position in that spot though.

I used -9999 in combination with the ORDER BY so that Add New would always be at
the top of the ComboBox list.

 

[Rob W]

Have I missed a parameter out? When i choose an existing record I get
message

'Error message "Update or CancelUpdate without AddNew or Edit"

More testing to be done ...

Its Access 2007.

Ive doen the combo box without the button wizard now, playign aroudn
with this code:-

Private Sub cboModulename_AfterUpdate()
If cboModuleName.Value <> "NEW" Then

' Find the record that matches the control.

Set rs = Me.Recordset.Clone

rs.FindFirst "[ModuleId] = '" & Me![cboModuleName] & "'"

If Not rs.EOF Then Me.Bookmark = rs.Bookmark

ElseIf cboModuleName.Value = "NEW" Then

If rs.AbsloutePosition = rs.RecordCount - 1 Then

DoCmd.GoToRecord , , acNewRec

End If


End If

End Sub

I set the moduleId to be "NEW" in the union query. I s these ao
reason for the -9999 value (I forgot to mention module ID is of type
text - 8 chars)

I dont understand why you are testing for the rs.AbsloutePosition in the
ElseIf block. If the user selects "Add New" then that is what they want
regardless of where they are currently positioned (correct?). I think you
DO need to test to see if they are already at the New Record position in
that spot though.

I used -9999 in combination with the ORDER BY so that Add New would always
be at the top of the ComboBox list.

 

[Rob W]

I've made a post on another access programming forum which explains my
problem, hopefully making it clearer as it contains code and images :-

http://www.access-programmers.co.uk/forums/showthread.php?t=144219

Thanks for all those who have helped me

Have I missed a parameter out? When i choose an existing record I get
message

'Error message "Update or CancelUpdate without AddNew or Edit"

More testing to be done ...

Its Access 2007.

Ive doen the combo box without the button wizard now, playign aroudn
with this code:-

Private Sub cboModulename_AfterUpdate()
If cboModuleName.Value <> "NEW" Then

' Find the record that matches the control.

Set rs = Me.Recordset.Clone

rs.FindFirst "[ModuleId] = '" & Me![cboModuleName] & "'"

If Not rs.EOF Then Me.Bookmark = rs.Bookmark

ElseIf cboModuleName.Value = "NEW" Then

If rs.AbsloutePosition = rs.RecordCount - 1 Then

DoCmd.GoToRecord , , acNewRec

End If


End If

End Sub

I set the moduleId to be "NEW" in the union query. I s these ao
reason for the -9999 value (I forgot to mention module ID is of type
text - 8 chars)

I dont understand why you are testing for the rs.AbsloutePosition in the
ElseIf block. If the user selects "Add New" then that is what they want
regardless of where they are currently positioned (correct?). I think
you DO need to test to see if they are already at the New Record position
in that spot though.

I used -9999 in combination with the ORDER BY so that Add New would
always be at the top of the ComboBox list.