How to edit a record/s in an undound textbox

[mar_t]

Good Day,
Basically I'm new in MS Access and I would say I belong to the user level. I did some reading and research and I was able to create my simple/basic database (w/ forms and reports). Through web research, I have created a simple record search. Now, what I would like to accomplish is to edit record/s using my frmAllStaff and save it. Below are my codes.

Please help.

Private Sub cboField_Click()

 End Sub
  Private Sub txtInput_AfterUpdate()
lstSource.RowSource = "SELECT StaffLastName, StaffFirstName, Section, Group, Unit, StaffDirectLine, StaffLocalNumber FROM tblAll  WHERE " & cboField & " ='" & txtInput & "'"
End Sub

[maximus]

This is what understand from you post:

1) You have a Unbound form.
2) You use this form to search Records

Now u want to update or Edit this record:

This is what I have done:

I have a table tblEmp

I have used a unbound form with a combobox to search data. When user selects a name in the combo box the details are autopopulated in unbound text boxes:

Then I have used a code on a command button to update any changes made to the data in the textboxes:

Private Sub Command9_Click()
On Error GoTo Err_Command9_Click
Set rst = CurrentDb.OpenRecordset("select * from tblEmp " & "where EmpID=" & Me.Combo0)
rst.Edit
rst!EmpName = Me.Text2
rst!EmpDOB = Me.Text4
rst!EmpAddress = Me.Text6
rst.Update
rst.Close
Set rst = Nothing

Me.Combo0.Requery

Me.Text2 = Null
Me.Text4 = Null
Me.Text6 = Null

'Dim strSQl As String
'strSQl = "Update tblEmp Set EmpName='" & Me.Text2 & "', EmpDOB=#" & Me.Text4 & "#, EmpAddress='" & Me.Text6 & "' Where EmpID=" & Me.Combo0
'CurrentDb.Execute strSQl, dbFailOnError
'Me.Combo0.Requery


Exit_Command9_Click:
Exit Sub

Err_Command9_Click:
MsgBox Err.Description
Resume Exit_Command9_Click

End Sub


The part of the code highlighted I have used RecordSet to update table in the second part of the code (which is commented in the DB attached I have used SQL. Both will work fine

Check the attached db

[mar_t]

Thank you Maximus, I'll try your file.

I have attached also my access file and hopefully you have time to look at it and do your magic.

Please refer to my file, what i want is that:
1. once a record or records has been found (based on my criteria's) and displayed in the "Search Result:" unbound box I would double click that record and it will launch my frmEdit form.
2. after the edit has been completed it will also clear the my search criteria fields and ready for the next search.

Please advise.

[maximus]

I have modified the file that u had attached and have done the following:

AllStaff Form:
1) Select The Search Field from the combobox
2) Type Criteria
3) Click Load Data Button

data pertaining to the criteria typed is loaded in the list box below. Click on any of the data on the List box the edit form for that data will open

e.g. Select EmpID in the combobox type 38 in the criteria txtbox and click Load Record Button the record is loaded in the List box below. Click on the record and the edit form for that record will open.

Code used:

Private Sub Command115_Click()
On Error GoTo Err_Command115_Click

Dim strSQL As String
Dim strCriteria As String

If Me.cboField = "StaffLastname" Or Me.cboField = "StaffFirstName" Or Me.cboField = "Group" Or Me.cboField = "Section" Or Me.cboField = "Unit" Then
strCriteria = " Where " & Me.cboField & " = '" & Me.txtInput & "'"
Else
strCriteria = " Where " & Me.cboField & " = " & Me.txtInput
End If


strSQL = "Select * From tblAllStaff" & strCriteria
Me.lstSource.RowSourceType = "Table/Query"
Me.lstSource.RowSource = strSQL
Me.lstSource.Requery


Exit_Command115_Click:
Exit Sub

Err_Command115_Click:
MsgBox Err.Description
Resume Exit_Command115_Click

End Sub

Private Sub lstSource_Click()
Dim strCriteria As String
strCriteria = "EmpID=" & Me.lstSource
DoCmd.OpenForm "tblEdit", , , strCriteria
End Sub


This code is to prevent the frmEdit to be opened without opening frmAllStaff. The code also clears the loaded data on the frmAllStaff

Private Sub Form_Close()
If CurrentProject.AllForms("frmAllStaff").IsLoaded = True Then

Forms!frmAllStaff!cboField = Null
Forms!frmAllStaff!txtInput = Null
Forms!frmAllStaff!lstSource.RowSource = ""
Forms!frmAllStaff!lstSource.Requery
End If
End Sub

Private Sub Form_Load()
If CurrentProject.AllForms("frmAllStaff").IsLoaded = False Then
MsgBox "This form cannot be opened as standalone"
DoCmd.Close
End If
End Sub


I am attaching the modified mdb file.

Pls mark th thread solved if your problem is solved.

[maximus]

the db attachment

[mar_t]

Hi Maximus,
It worked! thank you.. I did a couple of modification on the lstSource, instead of Click I change it DblClick

I used the "Delete Record" to delete a record, however the EmpID (or AutoNumber) got deleted also and this affects the total number of records on my table. I have a total of 176 records as displayed on the "Records Navigation" and it should appear as 175.
-Is there a way to retain the EmpID (AutoNumber) every time I use the Delete Record OR a code that will automatically generate number sequence everytime I ADD or DELETE record/s instead of the autoNumber?
-how to create a command button to delete a record or records on my lstSource

My apologies for having too many questions.

Marlon

[maximus]

Check this link out I think it has the answer to your question.
https://www.accessforums.net/forms/a...pens-5235.html

Lot of questions is good more questions you have we will have to come up with more answers. I think its a good exercise for all of us.

don't forget to mark the thread solved if you are able to solve your problem.