How to open form with blank fields rather thn first record!

[amb3r]

I have a form linked to an employees table. I have a search function on this form which allows you to search for a particular employee.
At the moment when i open my form, it always displays the first record!
How can I make my form open with blank fields instead of the first record showing?

Thanks alot.

Regards,
Amber

 

[RuralGuy]

If you turn on DataEntry the form will go to a *new* blank record. You would need to reset this property before you attempt to locate an employee.

 

[amb3r]

Thanks alot for your reply mate.

I actually tried doing that at first but unfortunately, I don't know how to reset the property so I left that approach!

 

[RuralGuy]

What method are you using to locate the particular employee record? Just put: Me.DataEntry = False in front of the code. Post back if you need additional assistance.

 

[amb3r]

I have a drop down box with the following method:
findEmpNo_AfterUpdate()

I've just tried your suggestion and i think it works! I'm just going to do a few tests to make sure all the data is saved etc

Thanks alot once again.

 

[RuralGuy]

Glad I could help Amber. You may want to put some code in the BeforeUpdate event of the form to keep the user from adding a new record.

 

[amb3r]

I was just going to mention that! haha
I have just done some testing and found out it's creating new records because of which i am getting an error message for 'creating duplicate values.'

What code is it that i need to put then please?

 

[RuralGuy]

Me.Undo
Cancel = True
Depending on what you are doing you may just want to lock all of the controls other than your ComboBox and just unlock them when it is ok to edit.

 

[amb3r]

grr still not working!

I put the code "Me.DataEntry = False" at the start of the findEmpNo_AfterUpdate() method. It allowed me to to search for employees but then caused an error because it created a new record.
I then put the code "Me.Undo" "Cancel = True" in the beforeupdate method of the form and it stopped creating new records.

But now whn i try searching for any employees, i get a run time error message: "The setting you entered isnt valid for this property"! :'(

Sorry for keep pestering you mate

 

[RuralGuy]

I think we need to see why your code creates a new record. How about posting all of the code in the AfterUpdate event of the ComboBox. It should not be creating a new record! New records are only created when you are on a new record and you "Dirty" the record. As long as you do not Dirty the new record while you are on it, Access should not try to actually save it. Maybe you could post a portion of your db that demonstrates the issue. It would be easier to troubleshoot.

 

[amb3r]

It has stopped creating new records since i put the code

Me.Undo 
Cancel = True

in the beforeUpdate method of FORM. However, it does not allow me to search for other employees anymore.

Here is the code for the combo Box:

Private Sub findEmpNo_AfterUpdate()

    Me.DataEntry = False

    ' Find the record that matches the control.
    Dim rs As Object
    On Error Resume Next
     
    Set rs = Me.Recordset.Clone

    rs.FindFirst "[EmpNo] = " & Str(Me![findEmpNo])
    
    Select Case rs.NoMatch
    Case True
        MsgBox "Employee Number does not exist!", vbInformation + vbOKOnly + vbExclamation, "Record Not Found"
    Case False
    '***********Update fields from "employees" table"***************
    DoCmd.RunSQL "UPDATE LiveEmployees INNER JOIN Employees ON LiveEmployees.EmpNo = Employees.EmpNo SET LiveEmployees.FName = Employees.FName, LiveEmployees.SName = Employees.SName, LiveEmployees.Name = Employees.Name;"
    
    '************************calculate some of the fields
    Me.Age = Me![AgeCal]
    'Me.LOS = Me![LOSCal]
    Me.TotalPay = Me![TotalPayCal]
        If Me.FTE.Value < 1 Then
        Me.FullPartTime = "Part-Time"
        Else
        Me.FullPartTime = "Full-Time"
        End If
    '****************End of Calculations / Updates ***********

    Me.Bookmark = rs.Bookmark
    End Select
  DoCmd.RunCommand acCmdRefresh
End Sub

Sorry, I understand it may be abit confusing as I've got alsorts of calculations etc going on in this method!

 

[RuralGuy]

Hi Amber,
I'm confused as to how you are using your form. I would force the user to select the Employee before anything else. It looks like that is the last thing you are doing and explains why you have "Dirty" records. You may want to rethink the logic flow of your form. Why is the Employee the last thing they do?

 

[amb3r]

Hi,

I've been tryng to figure this out for two days now but still no luck! :'(

Everytime I use the beforeUpdate method of FORM, it some how stops the Afterupdate method of the combobox (code shown in previous reply) from working!

I can't figure out why this is happening!

I would appreciate if anyone could help/advice me with with this plzzzz

Thanks,

Amber

 

[RuralGuy]

Amber,
Why is it necessary to do all of that updating when you locate the Employee? Why not just move to that record and then let the user update the bound fields on the screen? Because of the way you are using the form, the Form BeforeUpdate code I supplied will just get in the way, as you have discovered.

 

[amb3r]

Hi RG,
Thanks for your reply again hun.

The thing is the form i use is linked to a table called "LiveEmployees". I also have another table called "Employees" which gets uploaded data of employees(in excel format) every month!

I use the afterUpdate method of the combo box to search for an employee on my form. In this method i have also added some extra code so that it gets the most up to date data from "employees" table (e.g. address, salary etc) everytime i search an employee! This way i will always have uptodate data.

Anyway, now when i use the BeforeUpdate method of the FORM, then my AfterUpdate method of the combo box stops working! I even tried getting rid of all the extra code so that it only searches for an employee but even then the AfterUpdate method of the combo Box still does not work! I dn't understand why this happens. It always works fine until i add the BeforeUpdate method of the FORM. Its just driving me nuts!!

Hope this makes better sense (I doubt it though! lol)

 

[RuralGuy]

Try changing the cbo AfterUpdate code to:

Private Sub findEmpNo_AfterUpdate()

    Me.DataEntry = False

    ' Find the record that matches the control.
    Dim rs As Object
    On Error Resume Next
     
    Set rs = Me.Recordset.Clone

    rs.FindFirst "[EmpNo] = " & Str(Me![findEmpNo])
    
    Select Case rs.NoMatch
    Case True
        MsgBox "Employee Number does not exist!", vbInformation + vbOKOnly + vbExclamation, "Record Not Found"
    Case False
    '***********Update fields from "employees" table"***************
'    DoCmd.RunSQL "UPDATE LiveEmployees INNER JOIN ON Employees 'LiveEmployees.EmpNo = Employees.EmpNo SET LiveEmployees.FName = Employees.FName, LiveEmployees.SName = Employees.SName, LiveEmployees.Name = Employees.Name;"
    
    '************************calculate some of the fields
'    Me.Age = Me![AgeCal]
'    'Me.LOS = Me![LOSCal]
'    Me.TotalPay = Me![TotalPayCal]
'        If Me.FTE.Value < 1 Then
'        Me.FullPartTime = "Part-Time"
'        Else
'        Me.FullPartTime = "Full-Time"
'        End If
    '****************End of Calculations / Updates ***********

    Me.Bookmark = rs.Bookmark
End Select
'DoCmd.RunCommand acCmdRefresh
End Sub

...and let me know what happens.

 

[amb3r]

Hi RG,
Last night I tried the above but unfortunately it still didnt work. I just saved the code and called it a day. But this morning when i opened the database it worked perfectly!!!
I dn't even need to comment out the remaining code (the calculation bit), it all works now! its kind of weird because i've not really done anything different. i dnt understand why it wasnt working before but now all of a sudden it works.

Well atleast it works and thats what matters!
thank you very much for all your help once again RG.
Cheers,
Amber

 

[RuralGuy]

Glad to hear you got it working Amber. My experience is "If you didn't do anything to correct the problem then don't be too surprised when it comes back". Good luck with the rest of the project.

 

[AndrewS]

Resurrecting a very old thread (sorry) to add my solution.

I have the search for record combobox in the header of the form and the textboxes and other controls where users can actually view/edit data in the detail of the form.

Private Sub Form_Load()
  Me.Detail.Visible = False
End Sub

makes the detail section of the form, leaving just the header and its search box visible.

Then make it visible again when the user selects an entry in the search box.

Private Sub cboFindRecord_AfterUpdate()
   Me.Detail.Visible = True
   DoCmd.SearchForRecord , "", acFirst, "[PeopleID] = " & str(Nz(Me.cboFindRecord, 0))
   Me.cboFindRecord = ""
End Sub