autofill data entry form that shows only a blank record

[MilaK]

Please suggest how to autofill data entry form that shows only a blank record. The following code works great when 'data entry' is set to false but fails when data entry is set to true. Here what I've tried: http://allenbrowne.com/ser-24.html

I would like the user to type a unique patient indentifier into a text box on a form and Access would return the rest of the fields for the most recent record for that patient. This would be very simular to search, however, the user would be able to modify and save this information as a new record.

Do I need to query the table to find the last record based on a search criteria?

Thanks

 

[Ranman256]

you can set the properies to the controls to default value = value.
then when you open the form for data entry, it will fill those values.
docmd.openform "myForm",,,,acFormAdd

 

[MilaK]

you can set the properies to the controls to default value = value.
then when you open the form for data entry, it will fill those values.
docmd.openform "myForm",,,,acFormAdd

How will it know which patient I'm searching? The user needs to specify Patient id and the last record would be returned. I think I need to do something like this:

SELECT max(ID) "FROM tblPatients " _
      & "WHERE [Labcode] = '" & Me.Labcode.Value & "'" _

Correct??? and somehow I need to save it as a new record.

 

[sneuberg]

I don't think it's a good idea to use the ID to find the most recent record unless you are absolute certain that's the order you need. Note that Autonumbers are not guaranteed to be that way. It would be better if there were date you could use. But assuming that's all you have then I'd open a record set with something like

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblPatients WHERE [Labcode] = '" & Me.Labcode.Value & "' ORDER BY ID DESC")
If rs.EOF And rs.BOF Then
    MsgBox "Not Found"
    Exit Sub
End If
Me.txtSomeTextbox = rs!SomeFieldName
'....

rs.Close

The record you want, if there is one, would be first because of the descending sort on ID so then you just assign the field values to the form controls.

 

[MilaK]

I don't think it's a good idea to use the ID to find the most recent record unless you are absolute certain that's the order you need. Note that Autonumbers are not guaranteed to be that way. It would be better if there were date you could use. But assuming that's all you have then I'd open a record set with something like

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblPatients WHERE [Labcode] = '" & Me.Labcode.Value & "' ORDER BY ID DESC")
If rs.EOF And rs.BOF Then
    MsgBox "Not Found"
    Exit Sub
End If
Me.txtSomeTextbox = rs!SomeFieldName
'....

rs.Close

The record you want, if there is one, would be first because of the descending sort on ID so then you just assign the field values to the form controls.

Ok, I think this will be perfect because I don't need to display all of the fields by only particular fields and I will be able to control which fields I would populate

Would you use "Addnew" to save the modified records as a new record, or something else? Also, if the form is bound is it going to automatilly save the changes to the existing records? ( I don't want to modify the existing record) Should I just not bind the form? Thanks

 

[sneuberg]

The code is just reading the existing record so it should have any effect on it. If you go to a new record then run this code I think you'll be fine using a bound form. If you use an unbound form you will have to have an insert statement execute for all the events that normally trigger a save. Unless you don't want it to act like a bound form.

 

[MilaK]

The code is just reading the existing record so it should have any effect on it. If you go to a new record then run this code I think you'll be fine using a bound form. If you use an unbound form you will have to have an insert statement execute for all the events that normally trigger a save. Unless you don't want it to act like a bound form.

I get error 3464 Data type mismatch in criteria expression: here:

 Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblDSA WHERE [LABCODE] = '" & Me.tbLabcode.Value & "' ORDER BY ID DESC")

 Private Sub tbLABCODE_AfterUpdate()
 Dim rs As DAO.Recordset
 Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblDSA WHERE [LABCODE] = '" & Me.tbLabcode.Value & "' ORDER BY ID DESC")
 If rs.EOF And rs.BOF Then
    MsgBox "Not Found"
    Exit Sub
End If
Me.tbSampleNumber = rs!SampleNumber
 rs.Close
End Sub

Do you know what's causing the error?

Thanks

 

[sneuberg]

If LABCODE is a number then you don't want the single quotes. It would be:

 Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblDSA WHERE [LABCODE] = " & Me.tbLabcode.Value & " ORDER BY ID DESC")

If it's not that could you upload your database?

 

[sneuberg]

I suggest that you make tbLabcode a combo boxes with the row source being the distinct values of the LABCODEs. Then if you set the Limit to List of the combo to Yes the value of tbLabcode will pretty much have to be correct.