Data REport

[Johann]

Help!

[HansV]

Create a query that selects only the most recent record (if you want help with this, we'll need to have more information).
Set the Record Source of the report to this query.

[Johann]

Query is the table right?

[HansV]

No, a query is based on one or more tables. You can create a query in Access.

[Johann]

I dont have a query. I only have one table. All the records are saved there once the save command button is clicked in the program.

[HansV]

There doesn't seem to be a field in the table that specifies which is the most recent one. If you want to retrieve the most recently saved record, you will have to add a date/time field that you fill with the current date and time whenever the record is saved. Let's say you name this field LastModified.

Next, activate the Create tab of the ribbon.
Click Query Design.
Add the passenger table, then close the Show Table dialog.
Add the fields that you need, including the LastModified field.
In the Criteria row of the LastModified column, enter:

(SELECT Max(LastModified) FROM passenger)

Save the query as - for example - passengerquery.
Use passengerquery for your report instead of the passenger table.

[Johann]

I'm sorry for being a little slow. I don't get the part of LastModified (this will be a new column in passenger table?). In the command button save. I need to save also the date/time in the column of LastModified? Am I right?

[HansV]

Yes, LastModified will be a new field/column in the passenger table. A field of type Date/Time.
In the code that saves the record, set LastModified to Now. That is the current date and time.

[Johann]

What I did is this:


Dim Today as variant


Then I put it in the form_load()
Today = Format$(Now, "Long Date")

Then in the button that saves the record

rs.Fields("LastModified").value = Today


Is this acceptable?

And how can I connect the query instead of the table?

[Johann]

These are the codes I used =)


Dim rs As New ADODB.Recordset
Dim Connection As New ADODB.Connection
Dim today As Variant

Private Sub Command1_Click()
With rs
.Open "Select * from passenger", Connection, adOpenStatic, adLockOptimistic
End With
rs.AddNew
rs.Fields("Fname").Value = txtfname.Text
rs.Fields("Lname").Value = txtlname.Text
rs.Fields("Mname").Value = txtmname.Text
rs.Fields("Address").Value = txtaddress.Text
rs.Fields("Contact").Value = txtcontact.Text
rs.Fields("Adults").Value = frmFares.cboadult.Text
rs.Fields("Kids").Value = frmFares.cbochild.Text
rs.Fields("Senior").Value = frmFares.cbosenior.Text
If frmFares.lblamount.Caption >= 0 Then
rs.Fields("Balance").Value = "Paid"
End If
rs.Update
rs.Close
MsgBox "You have successfully added a new record in the database", vbInformation + vbOKOnly, "Success!"
Set rs = Nothing

txtfname.Enabled = False
txtlname.Enabled = False
txtmname.Enabled = False
txtcontact.Enabled = False
txtaddress.Enabled = False
End Sub

[HansV]

You don't need the Today variable. I'd insert a line

rs.Fields("LastModified").Value = Now

somewhere between rs.AddNew and rs.Update

How did you specify the passenger table as data source for the report? You should be able to specify passengerquery there instead of passenger.

[Johann]

With rs
.Open "Select * from passengerquery", Connection, adOpenStatic, adLockOptimistic
End With


I will change the passenger to passengerquery in the code above?

[HansV]

It looks like you already changed it

[Johann]

:D It means that what I did is correct? =D

[HansV]

Yes, that should work.

[Johann]

But the records will be saved also in the table? Even though I use the query? =).


what would be the code in the save button if (SELECT Max(LastModified) FROM passenger) is in the criteria in the query?

[Johann]

and in the connection. what should i use? the name of the table or the query? in the "rs.open " bla bla bla where name of table or query?

[HansV]

Wait. You should use passengerquery for the report, not for the form. The form should open a recordset on passenger, just like before.

[Johann]

What report? The data report in the Data Environment?

[HansV]

The one you showed in the first post in this thread.