Need Programming Help

[Guest]

Hello all,

How can I program the following steps into a Combo box that's "Bound to
Employees_ID".

1. Save current value of Employees_ID
2. Move to the next record selected from Combo box
3. Restore the value of Employees_ID from step 1.

-Simon

 

[Rick B]

Simon:

You don't do this. As I mentioned in my previous post, use a new control
for your FIND.

If you have a fields in a record, you don't start typing in it to find
records. You use a separate control for your find.

 

[Dirk Goldgar]

Hello all,

How can I program the following steps into a Combo box that's "Bound
to Employees_ID".

1. Save current value of Employees_ID
2. Move to the next record selected from Combo box
3. Restore the value of Employees_ID from step 1.

Are you sure you want to do exactly what you describe? Don't you just
want to move to the chosen record, without changing the employee ID for
the original record?

I agree with Rick B that you shouldn't do this, as even programming
around it leaves the *possibility* of accidentally modifying an employee
ID when you really only wanted to find a different employee. If you
insist on doing it this way, I can give you code to do it, but I
wouldn't feel 100% confident that it would always work without
possibility of error (if something causes the code to break, for
example).

May I suggest an alternative approach? Don't bind the combo box to the
field at all. Leave it unbound, but in the form's Current event, set it
to the value of the Employees_ID field of the current record. Note: to
do this, you have to give the combo box a name other than
"Employees_ID" -- maybe "cboEmployee". If the form allows additions,
you'll also have to use code in, maybe, the form's BeforeInsert event,
to get the value of the new autonumber -- if Employees_ID is one -- and
assign it to the combo box.

If the combo box is unbound, you can freely use it for navigation.

 

[Guest]

Rick,

I see your logic but I need to explain why my approach is required.

Let's say a record for "Mary Smith" already exists. Now, let's envision the
user changing the "Mary Smith" name to "John Williams" for some reason ( I've
seen users do this thinking a new record would be created ).

What happens now is Mary Smith's data appears to belong to John Williams.
Since I don't have any code in place to catch this, Access will happily allow
the change to take place without a fight.

So, for the reason listed above I decided to try using the method outlined
in my first post. Maybe there's a better approach, but I don't know what else
to try. Creating a second Combo Box as a lookup won't address the other
potential problem, right!

-Simon

 

[Guest]

Rick,

If I can't remember where I read about this but I think that this is
possible. I am out of state so I can't look through my books to find the
'school' solution. But if I had to do this brute force, I would write the
field to another table and read the information back to the table of interest
using some sort of On Open event.

Hopefully, someone has the answer for Simon.

LDN

 

[Guest]

Dirk,

Are you sure you want to do exactly what you describe? Don't you just
want to move to the chosen record, without changing the employee ID for
the original record?

Yes, that's exactly what I want to do. Problem is, I don't know how to code
this into the Combo Box that's used to select employee names. Any suggestions
or workarounds are most welcome!

-Simon

 

[Guest]

Dirk,

If you insist on doing it this way, I can give you code to do it, but I
wouldn't feel 100% confident that it would always work without
possibility of error (if something causes the code to break, for
example).

Dude, give me the code and let me test it out. If it doesn't work I'll try
your other suggested method, but right now I'm feeling lucky!

-Simon

 

[Dirk Goldgar]

Dirk,


Dude, give me the code and let me test it out. If it doesn't work
I'll try your other suggested method, but right now I'm feeling lucky!

Suit yourself.

'----- start of code for combo box -----
Private Sub EmpID_AfterUpdate()

Dim lngEmpID As Long

With Me.Employees_ID
lngEmpID = .Value
.Value = .OldValue
End With

Me.Recordset.FindFirst "[Employees_ID] = " & lngEmpID

End Sub
'----- end of code for combo box -----

Note that this code will force the current record to be saved -- whether
it was otherwise modified or not -- every time you choose a new employee
in the combo box.

 

[Jeff Boyce]

Simon

You've posted in a volunteer-supported newsgroup. None of the folks here
owe anyone anything -- "Dude, give me the code" may be more insistant than
you intended?

Good luck

Jeff Boyce
<Access MVP>

 

[Rick B]

I still don't understand. All the databases I've written and I've never
done anything like this.

The Name field tied to the record is just a name field. If the user changes
it, then yes, it will change the record.

If I'm reading your post correctly, you are not allowing a way to change the
name. What if there is a typo?

 

[Guest]

Jeff,

Please read thread "Re: Need Programming Help"

-Simon

 

[John Griffiths]

Hello all,

How can I program the following steps into a Combo box that's "Bound to
Employees_ID".

1. Save current value of Employees_ID
2. Move to the next record selected from Combo box
3. Restore the value of Employees_ID from step 1.

-Simon

Create un unbound combo somewhere appropriate i.e. header.
call it unboundEmployees_ID (for example).
set rowsource to query on Employees,
Employees_ID column 0 hidden,
column 1 to employees name field.

Set Limit To List to YES.
If not in list event fires create new employee record.
bound form to combo by setting form record source to
SELECT <your columns here>
FROM Employees
WHERE Employee_ID = Forms!<your form name here>!unboundEmployees_ID

This might need some tweaking but should give some idea of proceeding.

OK ? John