Next record with a blank status

[Vitiositas]

Hi guys, I am reposting a question i asked in the form section of the forums as i believe this question is best solved within the next record macro.

The database we are currently using just cycles through all the records in the client information table, which gets its data from an excel spreadsheet import.

What I need is to have my Next record button to only show those records that have nothing in the status field.

Another thing that i am trying to do is when someone clicks the next record button it will go to the next record that has no one in, to stop people from accessing the same records, not 100% sure this is possible tho. Thanks alot.

 

[Vitiositas]

Still need help

 

[AccessMSSQL]

Why don't you have a checkbox at the top of the form so users can check it to show only records that have no status. You would have to use VBA to do this and check to see if the checkbox value is true and then change the recordsource of the form.

In your checkbox after update event you would have something like this:

Private Sub Check1_AfterUpdate()
if check1.checked = true then
    me.recordsource = "select * from yourtable where status = "" or status is null"
else
   me.recordsource = "select * from yourtable"
end if
 
End Sub

as for the other issue, how many users do you have? And does everyone have their own local copy of the front-end? You aren't having multiple users access the same copy of the front-end, are you?

In order to "lock" records so users can't change a records someone else is working on you would have to flag the record whenever a user wanted to check it out or work on it. One way would be to add a flag column and username to your table. If you users logging in through windows domain accounts you could not allow a user to work on a record unless they checked it out. You would have a button on the main form that when the user checked it the record would be flagged to true and their windows user name would be populated in the username field. Then, you would have to modify your forms on_current event. On the current event of the form you would need to do a check to see if flagged is true and who the user is. You would then need to return a message to the user alerting that this record is already checked out.

Something like this:

Private Sub Form_Current()
If Environ("UserName") <> Me.UserName Then
    MsgBox ("This record is checked out by " & Me.UserName & ".  Cannot modify!")
    Me.AllowEdits = False
    Me.btnCheckOutRecord.Enabled = False[COLOR=green] 'this is your button to check out the record[/COLOR]
Else 
   Me.AllowEdits = True
   Me.btnCheckOutRecord.Enabled = True
   End If
End If

and here is your check out record button click event:

Private Sub btnCheckOutREcord_OnClick()
'check to see if current user already has this checked out
if me.flagged = true and me.username = environ("username") then
   msgbox("You already have this record checked out.")
   exit sub
end if
 
if me.flagged = false and me.username = "" then
   me.allowedits = true
   me.flagged = true
   me.username = environ("username")
   me.refresh
end if
 
end sub

You would also need to write code in there for the user to check the record back in when they are finished with it.

 

[Vitiositas]

Thanks alot for the reply,

Going to work on the first part right away.

In regards to the 2nd part about stopping people from seeing the same record, basicly the users log into the database through a simple user + password form which is linked to a table, the use of this database is to call people that have not recieved a response to their enquiry, we currently have around 20 people using 20 different frontends linked to 20 different sets of data in the backend to stop people calling the same enquiries. Generally each person is only in each record for around 10 seconds if no one answers then they move onto the next.

Do you think your suggestion will allow me to avoid having 20 different sets of data?

 

[AccessMSSQL]

Absolutely. You should not need 20 different tables if they are all the same structure. Definitely set up a test environment before you change anything. Are you using an .mdw file for the security? If user is only in record for 10 seconds then you would have to be sure the record gets checked in and checked out quickly. It might add one more step to the users job to have to click the check-out button. So I would probably avoid too many message prompts.

 

[Vitiositas]

Still working on this.

No i am not using a .mdw file, never heard of one to be honest, will give it a search sometime this afternoon.

 

[AccessMSSQL]

.mdw was Microsoft Access workgroup security in versions prior to Access 2007. So this allowed you to create permissions on objects and the database based on group membership. So a properly secured database would not be accessible without the .mdw file (workgroup security file). Microsoft removed this functionality in Access 2007 but still allows use of the MDW file if one exists for an older version of Access. For example, I was able to upgrade customers to Access 2007 and 2010 and retain use of the .mdw file.

 

[Vitiositas]

Ah okay, will have to be something I look into once our full server is set up.

With the records being locked to one user, is it possible for this to automaticly skip the in use records rarther than going into them and it saying it is locked?

 

[AccessMSSQL]

You can change the query of your form to select * from yourtable where locked = 0 and username <> Environ("UserName").

This might cause issues, however, because the recordset is refreshed and static so you would constantly have to be refreshing the recordsource.

Possibly try put this kind of code in the OnCurrent event:

If me.Locked = true and me.UserName <> Environ("UserName") then
  docmd.runcommand accmdRecordsGotoNext
end if

 

[Vitiositas]

Still plowing through this, will let you know if i come across anything i cant figure out