DataEntryForm, Retrieve Record, then Update Record with code. How?

[sisieko]

I have a form used for dataentry. A user places their request using this form (reservation form).

I also want "a certain user" [i enable certain fields for this user cboApprove(yes/no)] to retrieve records and update using this same form. This user changes the Approve(yes/no) combo box to Yes.
For example entrying the RequestID in a text box on the same form, should display all other info in thier appropriate texboxes when the user losses focus, so that the user can update record.
And the updated record should be able to save to the table NOT AS A NEW RECORD, but update existing record.

I have taken out the navigation bar. I have a cmdSave comand button for saving (place new request). And a cmdApprove button to confirm the Update (approve request).

What code do i need to achieve this.
To find the record (by entrying RequestID no. in a text box and display all other info in appropriate fields, then, update the record.
Or rather, how and what is the best way to achieve this.

Please help.

Yours truly,
Sisieko, aka Access Retard

 

[kjv1611]

Try using DLOOKUP and/or a recordset. For finding something in a table, DLOOKUP works great. Just open your form in design view, then click on the code button or open the VBA code window by selecting "View" from toolbar, then "Code". There, hit the help button, and type DLOOKUP in the Answer Wizard - gives examples and format, etc. For a recordset, lookup DAO.recordset or just DAO or just recordset in the answer wizard as well.

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[sisieko]

Thanks. I will go try that.
but will this populate all the fields on my form also?

What about update?

 

[sisieko]

Ok. I was readin the tutorial.
It seems like you are only able to display a value from a field in a "foreign table". But in my case, my form is based on this table. Only that it's a dataentry form (i set the dataenry property to "yes), meaning users can't really navigate.
But i want a certain user to access records using this same form and update the records.

 

[kjv1611]

Um, I think it works regardless of what table you want to pull from, just put in the format the name of the table, etc. I'll post an example I have down below (if I can find it):


Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[sisieko]

please thanks. That'd really help me.
I just got thrown into access over the past week. So im still finding my way around

 

[kjv1611]

I'll keep looking for DLOOKUP, but here is a DCOUNT example, which is the same thing, except it just counts records, kind of like a CountIf statement in Excel, but it searches about the same wasy as DLOOKUP, and pretty much the same format, I think... I'll find at least one example of DLOOKUP somewhere... [WINK]

    Me.txtAuditCount = DCount("[DNUM]", "tblStatusAudit", _
        "[Month]='" & cmbMonth & _
        "' AND [Team Leader]='" & cmbTeamLeader & _
        "' AND [Status]='" & cmbStatus & "' AND [Year]='" & cmbYear & _
        "' AND [Audit Type]='" & FrameAuditType & "'")

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[kjv1611]

Ahh.. got one, this is an example a coworker gave me when I first tried out using DLOOKUP and DCOUNT:

GetUserCode = DLookup("[UserCode]", "[tblUsers]", "[User Login] = '" & _
UserLogin & "'")

And one more:

MyCh = DLookup("[Chapter]", "[tblBK]", "LoanNum = '" & Loan_Num & "'")

Notes on this code (for better understanding, I hope):
1.)Basically, I'll first just rename the variables for easier understanding (b/c you won't have same names):
This is the same as the 1st example with more generic names, and a Dim statement:

Dim strMyVar as String
strMyVar = DLOOKUP("[[B]Field in tblYourTable[/B]]]", "[[B]tblYourTable[/B]]", _
"[[B]Some other Variable in tblYourTable[/B]] = '" & _
[B]SomeVariableOrControl[/B] & "'")

What this is doing in English is this:
Create a String variable, named strMyVar
Set strMyVar equal to "Field1", get "Field1" frmo tblYourTable(the name of your table = tblYourTable) for the records where "Field2"(some different variable, or can be the same variable if you want, I guess) equals to some other variable, value, environment variable, etc.

I hope this helps out some.. Best thing I can say is just give it a shot, and post any errors you get here along with the code relating to the error (I would include any variables defined, the DLOOKUP phrase, etc.)

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[sisieko]

Hey Stephen , thanks for your time.

I put this code under my AfterUpdate Event of txtRequestNum. The textbox where the user types in the requestNum to pull up the record.

Dim strMyVar As Variant
strMyVar = DLookup("[RoomReserved]","RequestTable", "[RecordNumber] = " _
& Forms!Form1!txtRequestNum)

This doens't populate any field. Nothing happens.

 

[PHV]

Take a look at the RecordSetClone, FindFirst, NoMatch and Bookmark properties/methods of the Form/RecordSet objects.

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

 

[sisieko]

This says "No current record" whereas, i have a record for the txtrequestNum i entered.

Private Sub txtRequestNum_BeforeUpdate(Cancel As Integer)
Me.RecordsetClone.FindFirst "[RecordNumber] = " & Me![txtRequestNum]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

 

[PHV]

Have you tried to move your code in the AfterUpdate event procedure ?

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

 

[kjv1611]

This is just a suggestion and a guess, sisieko, but can you not dim strMyVar as String instead of Variant?

strMyVar = DLookup("[RoomReserved]","RequestTable", "[RecordNumber] = " _
& Forms!Form1!txtRequestNum)

I just thought that could be a simple edit to test..

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[kjv1611]

Also, (I could be totally off base), but with PHV's idea which you tried (as post above), could you not type the control name as apposed to Me and see if that fixes that error?

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV