Return vs. Tab

  • Thread starter Thread starter jwrnana
  • Start date Start date
J

jwrnana

I have a form with date ranges. How do I change the format from tabbing
between dates versus using the enter/return key? Some of my forms use tab
and some use return. I would like to make them uniform.

Thanks,
JR
 
jwrnana said:
I have a form with date ranges. How do I change the format from
tabbing between dates versus using the enter/return key? Some of my
forms use tab and some use return. I would like to make them uniform.

Thanks,
JR
Click to expand...

Not sure I understand. <Tab> should always take focus to the next control
in the TabOrder. The <Enter> key will also do this as long as the current
Control's EnterKeyBehavior property is set to "Default". If that is changed
to "New line in field" then the <Enter> key will insert a line-break.
Normally one would only change to this setting in a TextBox that is bound to
a memo field and is more than one line tall.
 
Examples:
I have a Date Range Form (Between beginning and ending dates) that I use
when opening numerous forms. This particular form uses tab to move from the
beginning date to the ending date field.

I also have a query that has "Between date" as part of the criteria. When
the query is opened, I then must return after entering beginning date. Is
there a way to have a Date Range Form open the query? I prefer not to use a
report.

JR
 
jwrnana said:
Examples:
I have a Date Range Form (Between beginning and ending dates) that I
use when opening numerous forms. This particular form uses tab to
move from the beginning date to the ending date field.

I also have a query that has "Between date" as part of the criteria.
When the query is opened, I then must return after entering beginning
date. Is there a way to have a Date Range Form open the query? I
prefer not to use a report.
Click to expand...

Sure, just use DoCmd.OpenQuery instead of DoCmd.OpenReport behind a button
on the form.
 
Sorry for being so dense today. When using the a form or report, I can view
the properties behind the form/report and use the event procedure to add the
date range report. How do I add this in a Query?

Thanks
 
jwrnana said:
Sorry for being so dense today. When using the a form or report, I
can view the properties behind the form/report and use the event
procedure to add the date range report. How do I add this in a Query?
Click to expand...

You cannot make the query open the form. You have the form open the query.
 
Attached is the code behind my form. How do I make this open the query?
Sorry, but I only know how to attach this as an event procedure to a form.
Thank you for all of your assistance. JR

Private Sub Form_Open(Cancel As Integer)
Me.Caption = Forms![Report Date Range].OpenArgs
End Sub
Private Sub Preview_Click()
If IsNull([Beginning Order Date]) Or IsNull([Ending Order Date]) Then
MsgBox "You must enter both beginning and ending dates."
DoCmd.GoToControl "Beginning Order Date"
Else
If [Beginning Order Date] > [Ending Order Date] Then
MsgBox "Ending date must be greater than Beginning date."
DoCmd.GoToControl "Beginning Order Date"
Else
Me.Visible = False
End If
End If
End Sub
 
After several tries, I now have a form that opens my query. My form is as
below with beginning order date and ending order date. I removed the
criteria from the query for date range thinking that this form would do that
for me. However, now I have to enter the date range on the form AND when
the query opens. Can the second date range (query question) be avoided?
Also, the form remains on the screen rather than closing when the query
opens.

JR
jwrnana said:
Attached is the code behind my form. How do I make this open the query?
Sorry, but I only know how to attach this as an event procedure to a form.
Thank you for all of your assistance. JR

Private Sub Form_Open(Cancel As Integer)
Me.Caption = Forms![Report Date Range].OpenArgs
End Sub
Private Sub Preview_Click()
If IsNull([Beginning Order Date]) Or IsNull([Ending Order Date]) Then
MsgBox "You must enter both beginning and ending dates."
DoCmd.GoToControl "Beginning Order Date"
Else
If [Beginning Order Date] > [Ending Order Date] Then
MsgBox "Ending date must be greater than Beginning date."
DoCmd.GoToControl "Beginning Order Date"
Else
Me.Visible = False
End If
End If
End Sub



Rick Brandt said:
You cannot make the query open the form. You have the form open the query.
Click to expand...
Click to expand...
 
jwrnana said:
After several tries, I now have a form that opens my query. My form
is as below with beginning order date and ending order date. I
removed the criteria from the query for date range thinking that this
form would do that for me.
Click to expand...

No. Your criteria in the query needs to "look at" the controls on the form
for its values.
However, now I have to enter the date
range on the form AND when the query opens. Can the second date
range (query question) be avoided? Also, the form remains on the
screen rather than closing when the query opens.
Click to expand...

SELECT *
FROM TableName
WHERE DateField BETWEEN Forms!FormName!StartDate
AND Forms!FormName!EndDate
 
Following is the Code for my Form to Open the Query in Date Range format.
The Form asks for the dates - beginning and ending ship dates, but when the
query opens, the fields are blank AND the Date Form is still displayed on
the screen.

Private Sub Preview_Click()
If IsNull([Beginning Order Date]) Or IsNull([Ending Order Date]) Then
MsgBox "You must enter both beginning and ending dates."
DoCmd.GoToControl "Beginning Order Date"
Else
If [Beginning Order Date] > [Ending Order Date] Then
MsgBox "Ending date must be greater than Beginning date."
DoCmd.GoToControl "Beginning Order Date"
Else
Me.Visible = False
End If
End If
End Sub


Private Sub Form_Open(Cancel As Integer)

End Sub

Private Sub Run_JD_Info_Enter()
On Error GoTo Err_Run_JD_Info_Click

Dim stDocName As String

stDocName = "Query JD Delivery Info"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Run_JD_Info_Click:
Exit Sub

Err_Run_JD_Info_Click:
MsgBox Err.Description
Resume Exit_Run_JD_Info_Click
End Sub
 
jwrnana said:
Following is the Code for my Form to Open the Query in Date Range
format. The Form asks for the dates - beginning and ending ship
dates, but when the query opens, the fields are blank AND the Date
Form is still displayed on the screen.
Click to expand...

Then the SQL for your query is incorrect or there are no records in the range
you specified. Post the SQL of your query.

For the query to use the form it has to stay open.
 
The query works with or without the date range. It is below. When I use a
date range form to open a report, the date range form does not stay open.
Is it different for a query? You are very helpful. Thank you.

SELECT Orders.OrderDate, Contracts.ContractNum, Orders.PurchaseOrderNumber,
Customers.CompanyName, Customers.BillingAddress, Customers.City,
Customers.StateOrProvince, Customers.PostalCode, Orders.ShipName,
Orders.ShipAddress, Orders.ShipCity, Orders.ShipStateOrProvince,
Orders.ShipPostalCode, Orders.ShipCountry, [Order Details].LineItem, [Order
Details].Quantity, Products.ProductCode, Products.ProductName,
Customers.ControlNum, [Order Details].SerialNum, [Order Details].UnitPrice,
[Order Details]!UnitPrice*[Order Details]!Quantity AS [Total Price],
IIf([Total Price]>=0,[Total Price],Null) AS [Amount Before Trade-In],
IIf([Total Price]<0,[Total Price],Null) AS [Trade-In Amount], [Order
Details].OrderID, Orders.ShipDate, ([Amount Before Trade-In]*0.95) AS
[Dealere 95%], 0.05*[Amount Before Trade-In] AS [SEC 5%], Dealer.DealerName,
Dealer.ContactPerson, Dealer.City, Dealer.State, Dealer.UnitCode,
Dealer.DealerNum, Dealer.DealerType, [DlvDlrJDInv] AS [JD Invoice #],
[JDInv$] AS [JD Invoice Amount], Products.HandlingPct, [JD Invoice
Amount]*0.95 AS [Handling $]
FROM (Dealer INNER JOIN (Customers INNER JOIN Orders ON Customers.CustomerID
= Orders.CustomerID) ON Dealer.DealerID = Orders.DealerID) INNER JOIN
((Contracts INNER JOIN (SIN INNER JOIN Products ON SIN.SINID =
Products.SINID) ON Contracts.ContractID = Products.ContractID) INNER JOIN
[Order Details] ON Products.ProductID = [Order Details].ProductID) ON
Orders.OrderID = [Order Details].OrderID
WHERE (((Orders.ShipDate)>=[forms]![JD Daily Query Info]![Beginning
ShipDate] And (Orders.ShipDate)<=[forms]![JD Daily Query Info]![Ending
ShipDate]));
 
I just noticed that on the query, I am asking for a date range on ShipDate,
and on the form, Order Date. I changed that to ShipDate on both query and
form (query stays as below), and query works and using form to open query
gives me blanks.
jwrnana said:
The query works with or without the date range. It is below. When I use a
date range form to open a report, the date range form does not stay open.
Is it different for a query? You are very helpful. Thank you.

SELECT Orders.OrderDate, Contracts.ContractNum, Orders.PurchaseOrderNumber,
Customers.CompanyName, Customers.BillingAddress, Customers.City,
Customers.StateOrProvince, Customers.PostalCode, Orders.ShipName,
Orders.ShipAddress, Orders.ShipCity, Orders.ShipStateOrProvince,
Orders.ShipPostalCode, Orders.ShipCountry, [Order Details].LineItem, [Order
Details].Quantity, Products.ProductCode, Products.ProductName,
Customers.ControlNum, [Order Details].SerialNum, [Order Details].UnitPrice,
[Order Details]!UnitPrice*[Order Details]!Quantity AS [Total Price],
IIf([Total Price]>=0,[Total Price],Null) AS [Amount Before Trade-In],
IIf([Total Price]<0,[Total Price],Null) AS [Trade-In Amount], [Order
Details].OrderID, Orders.ShipDate, ([Amount Before Trade-In]*0.95) AS
[Dealere 95%], 0.05*[Amount Before Trade-In] AS [SEC 5%], Dealer.DealerName,
Dealer.ContactPerson, Dealer.City, Dealer.State, Dealer.UnitCode,
Dealer.DealerNum, Dealer.DealerType, [DlvDlrJDInv] AS [JD Invoice #],
[JDInv$] AS [JD Invoice Amount], Products.HandlingPct, [JD Invoice
Amount]*0.95 AS [Handling $]
FROM (Dealer INNER JOIN (Customers INNER JOIN Orders ON Customers.CustomerID
= Orders.CustomerID) ON Dealer.DealerID = Orders.DealerID) INNER JOIN
((Contracts INNER JOIN (SIN INNER JOIN Products ON SIN.SINID =
Products.SINID) ON Contracts.ContractID = Products.ContractID) INNER JOIN
[Order Details] ON Products.ProductID = [Order Details].ProductID) ON
Orders.OrderID = [Order Details].OrderID
WHERE (((Orders.ShipDate)>=[forms]![JD Daily Query Info]![Beginning
ShipDate] And (Orders.ShipDate)<=[forms]![JD Daily Query Info]![Ending
ShipDate]));

Rick Brandt said:
Then the SQL for your query is incorrect or there are no records in the range
you specified. Post the SQL of your query.

For the query to use the form it has to stay open.
Click to expand...
Click to expand...
 
jwrnana said:
I just noticed that on the query, I am asking for a date range on
ShipDate, and on the form, Order Date. I changed that to ShipDate on
both query and form (query stays as below), and query works and using
form to open query gives me blanks.
Click to expand...

I don't understand what you mean here. The query is getting the date values
from the form. How those controls might be labelled on the form is
irrelevant. I see nothing wrong with your query on first examination (at
least as it pertains to the date-range).

If you temporarily remove the criteria for date range from the query do you
get records then? If you do try a ridiculously large range to see if you
get anything. 1-1-1900 to 12-31-2050 for example.

You might also need to identify the form references as parameters in your
query and explicitly indicate that they are DateTime DataTypes. Access will
usually figure this out on its own based on the structure of the data but it
never hurts to add the parameters just in case.
 
Lets try again!

I created a new form. Now, I am getting the information on the form with
the following problems:

I enter the beginning date and the ending date, this then opens the query,
BUT again, I must input the same date info because the query has criteria
referencing the new form. Then I get the correct information. If I do not
have the criteria on the query, I get no information. My reason for adding
a form to open the query is the format requested on the date range from the
parameter in the query.

Same question -- why does the date form have to stay open on the query, when
it closes on the reports that I use it for?
 
jwrnana said:
Lets try again!

I created a new form. Now, I am getting the information on the form
with the following problems:

I enter the beginning date and the ending date, this then opens the
query, BUT again, I must input the same date info because the query
has criteria referencing the new form.
Click to expand...

Then the references are incorrect. Any time a query prompts you for
parameters it means that you typed something into the design of the query
that Access cannot find (so it asks the user). If you correctly point the
criteria at the controls on the form (and the form is open when the query
runs) then you will not be prompted by the query.
Then I get the correct
information. If I do not have the criteria on the query, I get no
information. My reason for adding a form to open the query is the
format requested on the date range from the parameter in the query.

Same question -- why does the date form have to stay open on the
query, when it closes on the reports that I use it for?
Click to expand...

I have no idea how your report is structured or the query it uses, but if
you are using OpenReport and passing the values from the form in the WHERE
clause argument then the values are being PASSED to the report which means
the form is no longer required.

Have you tried using the builder to create the form references instead of
typing them? That way they are guaranteed to be correct.

You cannot pass values to a query so if the query is referencing a control
on a form then that form must be open at the time the query is run.
 
Back
Top