New Record issue

[Linda RQ]

Hi Everyone,

Access 2003.

First Issue:
I have a main data entry form. It has 2 subforms on it. There is a record
selector for the form. If my user makes an error and wants to delete the
record they can select the record and delete...The problem is that it seems
like it deletes the record but when you close and open the databasee it
comes back. I have a front end and back end to this database. The main
form is PtInfo, the subforms are PtTherapy and PtLocation. The tables are
related by PtID. The only event I have on my form is a macro to maximize
it.

Second Issue:
When we add a new patient we have to close the database out and open it for
the record to show up on our reports.

Thanks,
Linda

 

[Guest]

First, how are you deleting the record? It may not actually be deleting the
record if it is still there.

As to a new record not being available, to get it into your recordset, you
have to requery the form. Me.Requery

Now, there is an issue with this. That is that when you requery the form,
it goes back to the first record in the recordset. To requery a form and
make it stay on the current record you have to save the key value for the
record, do the requery, then move back to the record.

lngKeyVal = Me.KeyControl
Me.Requery
With Me.RecordsetClone
.FindFirst "[KeyField] = " & lngKeyVal
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End if
End With

 

[Linda RQ]

Thanks Dave.

I have just really started to enter code into my database. What event
should I enter this code into.....I am thinking after update? What does
lngKeyVal mean and would I put PtID in the brackets [KeyField]. Not sure if
this would help but here is the sql for the query that my main form is based
on.

SELECT tblPatients.PtLName, tblPatients.PtFName, tblPatients.PtID,
tblPatients.MRNum, tblPatients.AdmitNum, tblPatients.AdmitDtTm,
tblPatients.DCDtTm, tblPatients.PtMI, tblPatients.PtDOB,
tblPatients.PtActive, tblPatients.Isolation, tblPatients.PtMemo,
tblPatients.Diagnosis, tblPtLocation.PtLocRmNum, tblPtLocation.PtLocEnDtTm,
tblPtLocation.BedID_fk
FROM tblPatients LEFT JOIN tblPtLocation ON tblPatients.PtID =
tblPtLocation.PtID_fk
WHERE (((tblPatients.PtActive)=Yes) AND ((tblPtLocation.PtLocEnDtTm) Is
Null))
ORDER BY tblPatients.PtLName, tblPatients.PtFName;

Linda

First, how are you deleting the record? It may not actually be deleting
the
record if it is still there.

As to a new record not being available, to get it into your recordset, you
have to requery the form. Me.Requery

Now, there is an issue with this. That is that when you requery the form,
it goes back to the first record in the recordset. To requery a form and
make it stay on the current record you have to save the key value for the
record, do the requery, then move back to the record.

lngKeyVal = Me.KeyControl
Me.Requery
With Me.RecordsetClone
.FindFirst "[KeyField] = " & lngKeyVal
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End if
End With
--
Dave Hargis, Microsoft Access MVP

Hi Everyone,

Access 2003.

First Issue:
I have a main data entry form. It has 2 subforms on it. There is a
record
selector for the form. If my user makes an error and wants to delete the
record they can select the record and delete...The problem is that it
seems
like it deletes the record but when you close and open the databasee it
comes back. I have a front end and back end to this database. The main
form is PtInfo, the subforms are PtTherapy and PtLocation. The tables
are
related by PtID. The only event I have on my form is a macro to maximize
it.

Second Issue:
When we add a new patient we have to close the database out and open it
for
the record to show up on our reports.

Thanks,
Linda

 

[Guest]

The code would go in the Form's After Update event.
As to lngKeyVal, that is just an example where I am assuming an autonumber
primary key. What you would use would be the actual data type of whatever
your primary key is. If it is PtID, then you would use it's data type to
save the key value of the record. Brackets in this case are not necessary,
it is my personal habit to put brackets on all field names. Don't confuse
field name with a control name. Fields belong to tables and queries.
Controls are on forms and reports.
--
Dave Hargis, Microsoft Access MVP

Thanks Dave.

I have just really started to enter code into my database. What event
should I enter this code into.....I am thinking after update? What does
lngKeyVal mean and would I put PtID in the brackets [KeyField]. Not sure if
this would help but here is the sql for the query that my main form is based
on.

SELECT tblPatients.PtLName, tblPatients.PtFName, tblPatients.PtID,
tblPatients.MRNum, tblPatients.AdmitNum, tblPatients.AdmitDtTm,
tblPatients.DCDtTm, tblPatients.PtMI, tblPatients.PtDOB,
tblPatients.PtActive, tblPatients.Isolation, tblPatients.PtMemo,
tblPatients.Diagnosis, tblPtLocation.PtLocRmNum, tblPtLocation.PtLocEnDtTm,
tblPtLocation.BedID_fk
FROM tblPatients LEFT JOIN tblPtLocation ON tblPatients.PtID =
tblPtLocation.PtID_fk
WHERE (((tblPatients.PtActive)=Yes) AND ((tblPtLocation.PtLocEnDtTm) Is
Null))
ORDER BY tblPatients.PtLName, tblPatients.PtFName;

Linda

First, how are you deleting the record? It may not actually be deleting
the
record if it is still there.

As to a new record not being available, to get it into your recordset, you
have to requery the form. Me.Requery

Now, there is an issue with this. That is that when you requery the form,
it goes back to the first record in the recordset. To requery a form and
make it stay on the current record you have to save the key value for the
record, do the requery, then move back to the record.

lngKeyVal = Me.KeyControl
Me.Requery
With Me.RecordsetClone
.FindFirst "[KeyField] = " & lngKeyVal
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End if
End With
--
Dave Hargis, Microsoft Access MVP

Hi Everyone,

Access 2003.

First Issue:
I have a main data entry form. It has 2 subforms on it. There is a
record
selector for the form. If my user makes an error and wants to delete the
record they can select the record and delete...The problem is that it
seems
like it deletes the record but when you close and open the databasee it
comes back. I have a front end and back end to this database. The main
form is PtInfo, the subforms are PtTherapy and PtLocation. The tables
are
related by PtID. The only event I have on my form is a macro to maximize
it.

Second Issue:
When we add a new patient we have to close the database out and open it
for
the record to show up on our reports.

Thanks,
Linda