Is it possible to limit the records displayed in a subform

[shabbaranks]

Hi...



Is it possible to hide previously added records which have been added to a subform? For example a user adds thier selections and closes the database. They then re-open the database and rather than displaying thier previous selection - its blank ready to start again?

Hope I'm clear on this?

Thanks
shabba!

[Toyman]

Shabba, since you use codes to enter the values to the table directly and use the subform to display the result, you cannot just set the subform's property to an entry form. Instead, based on your set up, you can set up a temp table based on the timesheet table. Have the subform bound to the temp table instead. As you exit the form, write a code to append the content of the temp table onto your timesheet table and then deleting the contents of the temp table. This way, the records are shown when your user enters the data. Once they exit the form, the temp table is wiped clean allowing the next user a blank table with no records

[Rawb]

You'll need to provide more information before we can answer. . .

Do you want the Records to remain in the Database and just not be shown anymore or are you wanting to completely remove the information?

What is the Form/Subform used for? If we can know what you're trying to do, we may be more able to figure out how to do it for your specific application.

[shabbaranks]

Thanks guys, and Im with ya Toyman. Thanks for your help previously by the way

With this temp table setup. If there are multiple using the database would this cause a problem? Or is it best to link the tables to a central table and have local copies on all the client machines?

Thanks again

[Rawb]

In a case like that, it's best to use separate "client" databases on user workstations that all connect to a central "backend" database on a server somewhere.

For development purposes, you can create the entire thing as a single database. Then, when you're done, just use Access' Database Splitter to separate the "client" and "backend" databases.

[Toyman]

In a case like that, it's best to use separate "client" databases on user workstations that all connect to a central "backend" database on a server somewhere.

For development purposes, you can create the entire thing as a single database. Then, when you're done, just use Access' Database Splitter to separate the "client" and "backend" databases.

I agree with Rawb, you need to set up a frontend/backend setup. Each user should have a frontend set up in their computer. The backend (one only) is set up in a centralized location such as a server etc. All of your frontends are then linked to the backend via link tables. The temp table I mentioned is set up in the frontend level. This way, each user can have temp data without affecting others. Once the form closes, use an append query to append the data to the main table in the backend.

[shabbaranks]

Great thanks, I got my query working but I wanted to add the code to a VBA - but suprise suprise it doesnt work. Can you see what Ive done wrong?

Private Sub ExitAccess_Click()
Mysql = "INSERT INTO TimesheetTable ( ID, sUser, Activity, Department, Hours, Project, [Task Date], Description ) Values "
Mysql = Mysql & "('" & TimesheetTableTemp.ID & "', '" & TimesheetTableTemp.sUser & "', '" & TimesheetTableTemp.Activity & "', '" & TimesheetTableTemp.Department & "', '" & TimesheetTableTemp.Hours & "', '" & TimesheetTableTemp.Project & "', '" & TimesheetTableTemp.[Task Date] & "', '" & TimesheetTableTemp.Description & "'"
DoCmd.RunSQL "DELETE * FROM TimesheetTableTemp"
DoCmd.SetWarnings False
DoCmd.Quit
End Sub

Thanks

[shabbaranks]

Ive amended my VBA but still the button doesnt work

strSql = "INSERT INTO TimesheetTable " & vbCrLf & _
"SELECT TimesheetTableTemp.* " & vbCrLf & _
"FROM TimesheetTableTemp;"

Thanks

[Rawb]

It looks like you're just saving the Query string to a variable and then not actually running it.

Try this:

Private Sub ExitAccess_Click()
  DoCmd.SetWarnings False ' So you aren't asked to confirm the Append or Delete actions
  DoCmd.RunSQL "INSERT INTO TimesheetTable " & _
               "SELECT TimesheetTableTemp.* " & _
               "FROM TimesheetTableTemp;"

  DoCmd.RunSQL "DELETE * FROM " & _
               "TimesheetTableTemp"
  DoCmd.SetWarnings True ' ALWAYS turn this back on!!!

  DoCmd.Quit
End Sub

Also (and this is ENTIRELY a personal preference), I try to stay away from variable names like "Mysql" or Msql" or the like. Both MySQL and mSQL happen to be other database engines!

[shabbaranks]

Thanks Rawb, so when you say you try to stay away from Mysql etc I notice in the above example your using DoCmd.RunSQL <-- obviously this is different to the mysql and sql command but how? Thanks again

[Rawb]

The terms MySQL, mSQL and Access (and PostgreSQL along with many many others) all refer to different. . . programs I guess you could say. When you talk about MySQL, you're not talking about Access, but a different program that simply does many of the same things.

But my primary reason for suggesting you stay away from using those specific variable names is because MySQL and the others each do things a little differently: For example, in Access you would use the following Query

SELECT
    TOP 10
    *
FROM
    MyTable
WHERE
    MyField=10

That same Query in MySQL is

SELECT
    *
FROM
    MyTable
WHERE
    MyField=10
LIMIT 10

Small changes, sure, but ones that can cause confusion nonetheless.

DoCmd.RunSQL is a command in Access that runs a Query. For example, look at the following code:

Dim strSQL as String

strSQL = "SELECT TOP 10 * FROM MyTable WHERE MyField=10"

DoCmd.RunSQL

The above code, doesn't actually do anything! Why? Because DoCmd.RunSQL is on a line by itself, with no Query actually being run. Instead, you would use the following code:

Dim strSQL as String

strSQL = "SELECT TOP 10 * FROM MyTable WHERE MyField=10"

DoCmd.RunSQL strSQL

This tells Access to run the SQL Query stored in the variable strSQL.

[shabbaranks]

Ah I get you, I think previusly I had a little mysql a little msql and a little Do..RunSQl. Thats great I think I understand a little better now