Enter data in an empty field of an existing record.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This has got to be the easiest thing to do in a database, but I just cannot
figure it out. I have a multi user database. It has "startclock" and
"stopclock" fields in a table. The form Control Source is the table. What I
want to do is retrieve the record for a specific employye where the
"startclock" field has an entry and the "stopclock" field is empty. I've
tried using a combo box to simply retrieve "any" record for the employee and
keep getting a "not a current record" error. The table columns are:
OPID
FirstName
LastName

I have the control source column set at 1 because if I set it to 0
Me![Test] is one digit less than the what it should be (OPID) - and still get
the same error.

The code is below. "Test" is the combobox name and it is unbound.
"ClockOut" is one of several option buttons.

Private Sub ClockOut_GotFocus()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.findfirst "[OPID]='" & Me![Test] & "'"
Me.Bookmark = rs.Bookmark
End Sub

Can anyone tell me what I am missing?
 
Walt,

Is OPID a number data type, or text? If it's number, you need...
rs.FindFirst "[OPID]=" & Me![Test]
 
Thank you Steve. That worked perfectly. I'm assuming then that the trailing
&" ' " in my original code is used for text fields. This project gets more
interesting every day. Now I've learned that I have to change the DATA ENTRY
property to "yes" when creating a new record, and "no" when I want to search
for and update a current record. It's a lot like crossing a freeway in
traffic - the objective is easy, but getting there is all kinds of fun.
Thanks Again.

Steve Schapel said:
Walt,

Is OPID a number data type, or text? If it's number, you need...
rs.FindFirst "[OPID]=" & Me![Test]

--
Steve Schapel, Microsoft Access MVP

Walt said:
This has got to be the easiest thing to do in a database, but I just cannot
figure it out. I have a multi user database. It has "startclock" and
"stopclock" fields in a table. The form Control Source is the table. What I
want to do is retrieve the record for a specific employye where the
"startclock" field has an entry and the "stopclock" field is empty. I've
tried using a combo box to simply retrieve "any" record for the employee and
keep getting a "not a current record" error. The table columns are:
OPID
FirstName
LastName

I have the control source column set at 1 because if I set it to 0
Me![Test] is one digit less than the what it should be (OPID) - and still get
the same error.

The code is below. "Test" is the combobox name and it is unbound.
"ClockOut" is one of several option buttons.

Private Sub ClockOut_GotFocus()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.findfirst "[OPID]='" & Me![Test] & "'"
Me.Bookmark = rs.Bookmark
End Sub

Can anyone tell me what I am missing?
Click to expand...
Click to expand...
 
Now I've learned that I have to change the DATA ENTRY
property to "yes" when creating a new record, and "no" when I want to search
for and update a current record.
Click to expand...

Umm..!?

No, that should NOT be necessary, and in fact it makes no sense.

So long as you have Allow Additions set to be true, you can use the
same form for entering new data as you use for searching or editing
existing data. Just click the *> navigation button to go to the new
record.

John W. Vinson[MVP]
 
Thanks for the response John. I was surprised that I have to do this too.
However, my users know little to nothing about Access, and I am using a combo
box (operator's name) to either call an existing record or create a new one.
This is a labor accounting DB and I'm trying to capture everything from
clock-in, breaks, work order changes, and clock-out for each employee. I'm
currently thinking of creating a 2 field table that has operator and today's
date (the date field would clear each day) , and based on whether or not the
date is there, the Data Entry property would be set to yes or no. I may be
unintentionally doing something to cause this to be necessary.
 
Thanks for the response John. I was surprised that I have to do this too.
However, my users know little to nothing about Access, and I am using a combo
box (operator's name) to either call an existing record or create a new one.
Click to expand...

I'm still perplexed. A combo box, by itself, CANNOT be used to create
a new record. Could you explain?
This is a labor accounting DB and I'm trying to capture everything from
clock-in, breaks, work order changes, and clock-out for each employee. I'm
currently thinking of creating a 2 field table that has operator and today's
date (the date field would clear each day) , and based on whether or not the
date is there, the Data Entry property would be set to yes or no. I may be
unintentionally doing something to cause this to be necessary.
Click to expand...

I'm all but sure that you are.

Again: why can't you just navigate to the new record (using
DoCmd.GoToRecord acForm, Me.Name, acNewRecord in code perhaps) rather
than toggling Data Entry view????

John W. Vinson[MVP]
 
Thanks again John,
I apologize for misleading you with the combo box statement - I'm just using
the combo box to get the employee's info. I wasn't aware of the DoCmd
statement you quoted, and maybe it will work for me. Yes, I know I am
causing the problem with edit/add records, and I'm sure it is in my code, I
just haven't found it yet. Toggling the DataEntry property doesn't work.

One of my current opportunities is trying to figure out how to use two
criteria for retrieving a record (the employee ID and one other numeric field
relating to the type of entry: break, lunch, etc.). When a person clocks
in, the start time field is populated, but the stop time isn't until end of
shift. During the day that person will take two breaks and a lunch, each of
which is a record - creating a new record with the start time is easy, but
getting back to the proper record to enter the stop time is the challenge. I
could use "findlast" for all except the clock in/out record. I tried using a
Do/Loop with a "findnext" statement looking for a match in the "type" field,
but that doesn't work. I know you don't have time to walk me through this
entire project, but any help is greatly appreciated.
 
I tried using a
Do/Loop with a "findnext" statement looking for a match in the "type" field,
but that doesn't work. I know you don't have time to walk me through this
entire project, but any help is greatly appreciated.
Click to expand...

I'd be glad to help, but I would need to know what I have to work
with. Could you post a description of your table structure, with the
relevant fieldnames, their datatypes, and a very brief description of
what's in the field if it's not obvious?

John W. Vinson[MVP]
 
Sure John. I'm very pleasently surprised and thankful that you're sticking
with me on this. Some of these things are so simple and yet so frustrating.
I do have the add/edit problem worked out. Now I just have to be able to
pull up a specific record.
Here is the file structure:
RecordID - AutoNumber (Record #)
OPID - Number (Employee ID)
WOID - Number (Workorder, Clock-in/out, Lunch-in/out, Break-in/out)
Start - Date/Time (T9ime start event - system clock)
Stop - Date/Time (Time stop event - system clock)
Workdate- Date/Time (Event Date -today)
Category - Number (Normal, Sick, Vac, etc.)

WOID is one of the keys, and is either a 3 digit number based on the value
of an option button group or an 8 digit workorder number.
An employee should typically have no more than two open events (Stop Time
not entered) in a given day. They would be the "Clock in/out" record and
either one of two breaks or a lunch record. I have everything working for
creating the event record, and I can retrieve the last record for the
employee, but I need to be able to retrieve a record based on the employee
and a choice of either of those two records, since I don't want to close the
Clock In/Out record until the end of the day. I don't want the employee to
select from a datasheet view, so I'm using an option button group to select
the type of event to be closed. I thought about using the RecordID, but
couldn't figure a way to tie that to the option buttons.

"OPID" will identify the employee using ComboBox X and "WOID" will identify
the record type which is recorded in the form object named "WorkOrder".

I believe then that I need VB code to achieve something like:
Findfirst record where OPID=ComboBox X and WOID=WorkOrder.
I tried (unsuccessfully):
Find first record where OPID=ComboBox X
do until WOID=WorkOrder
Find next record
loop
 
Back
Top