Retreive/Populate fields and Update.

[sisieko]

I am trying to retrieve records and populate the other fields in a form. I'd type in the requestnumber (pk) in a text field on a form bound to a table tblRequestTable, and it should populate the rest of the fields.

But what this is doing is, it retrieves the record, and when i close it, it saves as a new record, instead of updating. what am i doing wrong?

strSQL = "Select * FROM RequestTable WHERE RequestNumber = " & Me.txtRequestNum

Set Db = CurrentDb
Set Rs = Db.OpenRecordset(strSQL, dbOpenDynaset)
Rs.MoveFirst
With Rs
    Me.txtDateOfRequest = .Fields("DateOfRequest")
    Me.txtRequestApproval = .Fields("RequestApproval")
    Me.txtDateOfApproval = .Fields("DateOfApproval")
    Me.txtDateReserved = .Fields("DateReserved")
    Me.StartTime = .Fields("StartTime")
    Me.EndTime = .Fields("EndTime")
    Me.txtRoomReserved = .Fields("RoomReserved")
    Me.txtProgram = .Fields("Program")
End With
Rs.Close
Set Rs = Nothing

please help

 

[RoyVidar]

Erm - is this an ordinary lookup?

If so, just use something like this in stead (in the after update of your text control):

[tt]dim rs as dao.recordset
set rs=me.recordsetclone
rs.findfirst "RequestNumber = " & Me.txtRequestNum
if not rs.nomatch then me.bookmark=rs.bookmark
set rs=nothing[/tt]

- or just use the combo wizard, which creates similar code when using the "Find a record on my form..." option.

What you're doing, if this is a bound form, is probably either altering what ever record in the form being the current record (if it's a new record, it would probably create a new one - but should throw an error on the PK, I think, if it's not an autonumber)

Roy-Vidar

 

[sisieko]

i tried that, nothing happened, it didn't populate the other fields.

I am trying to pullup a record from a table so as to update it and save it back to the table.

what's the combo wizard, or how d'u do it.?

 

[RoyVidar]

Change to:

[tt]if not rs.nomatch then
me.bookmark=rs.bookmark
else
msggox "no match was found"
end if[/tt]

The combo wizard is most often found when the Wand (the second button of the toolbox) is "selectee/clicked", and then add a combo to the form. Third option, when using bound forms, is usually "Find a record..."

But how do you store this information, the code above only fetches it and populates the controls...

Things on forms and reports are often referred to as controls. Things in tables as fields. Controls can be bound to fields (when working in a bound form - a form having a table/query as record source)

Roy-Vidar

 

[sisieko]

Dim MyRS As DAO.Recordset
Dim Criteria As String                    
Dim SearchString As String

Set MyRS = Me.RecordsetClone

SearchString = Me.txtRequestNum
Criteria = "RequestNumber Like" & Chr(39) & SearchString & Chr(42) & Chr(39)   
MyRS.FindFirst Criteria
If MyRS.NoMatch Then
    MsgBox "Could not Locate Requestnumber " & SearchString
Else
     Me.Bookmark = MyRS.Bookmark
End If
Me!txtRequestNum = Null
MyRS.Close

i enter a RequestNumber and even when the Record exist i get the message: "Could not Locate Requestnumber 2" for instance. as in what i specified in my msgbox.


ps: thanks for enlightening me on the combo.

 

[PHV]

Have you tried this ?
Criteria = "RequestNumber=" & SearchString

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244