Record search error

[Guest]

Access 2003,

I currently have a form which is used to search for and update records. The
field from which i search is a text box and is unbound and the search is by
Request ID.

When i type in a search which is not in the list, instead of keeping the
blank record which isbegins with, it brings up the first record. Hence, the
agents, if they are not paying enough attentino will not realise that the
record is incorrect.

If my table is called Acceptance and search field called Combo24 (not a
combo box), what code would I need to right in order to stop this from
happening.

Matt

(Klatuu, our old convo did not finish, apologies)

 

[Guest]

Matt,

This is the code that I typically use:

On Error Resume Next
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "YourForm"

stLinkCriteria = "[YourField] =" & Me![YourUnboundNumericalCombo]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Me![YourFirstField].SetFocus


If the Bound Column of your combo is a text field, replace the line above
with:

stLinkCriteria = "[YourField] =" & "'" & Me![YourUnboundTextCombo] & "'"

Sprinks

 

[Guest]

It sounds like you are using the form's recordset to do the search, so when
nothing is found, it ends up at the last record. It is unusual to use a text
box for a search, but if you must, I would suggest you check for the value is
in the table before you start messing with the form's recordset. I would
suggest the Before Update of your search text box.

Dim rst As Recordset

Set rst = Me.RecordsetClone
rst.FindFirst "[SearchField] = '" & Me.txtSearchFor & "'"
If rst.NoMatch Then
MsgBox "Record Not Found"
Cancel = True
Else
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing.

 

[Guest]

Klatuu,

How would i usually have a search then? The only way i could get it to work
was to unbound it and have it as a text box. I am sure there was an easier
way e.g. Bound by combo or something but every time i tried to type in the
search it said i was duplicating data.

Sprinks,

Where would i enter this? ONLoad?

It sounds like you are using the form's recordset to do the search, so when
nothing is found, it ends up at the last record. It is unusual to use a text
box for a search, but if you must, I would suggest you check for the value is
in the table before you start messing with the form's recordset. I would
suggest the Before Update of your search text box.

Dim rst As Recordset

Set rst = Me.RecordsetClone
rst.FindFirst "[SearchField] = '" & Me.txtSearchFor & "'"
If rst.NoMatch Then
MsgBox "Record Not Found"
Cancel = True
Else
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing.

Access 2003,

I currently have a form which is used to search for and update records. The
field from which i search is a text box and is unbound and the search is by
Request ID.

When i type in a search which is not in the list, instead of keeping the
blank record which isbegins with, it brings up the first record. Hence, the
agents, if they are not paying enough attentino will not realise that the
record is incorrect.

If my table is called Acceptance and search field called Combo24 (not a
combo box), what code would I need to right in order to stop this from
happening.

Matt

(Klatuu, our old convo did not finish, apologies)

 

[Guest]

Typically, you would use an unbound Combo. The Combo control is ideally
suited for doing this because it has the Autoexpand property which, when set
to Yes, does the "type ahead" thing and allows the user to find the value
quickly without having to type in the entire value. It also has the Not In
List property which either prevents or allows users to enter values that are
not in the row source of the combo. This is usually used to allow adding new
records to a table.

The code I posted earlier for the text box will work for the combo as well.
Here is an example of the Not In List event that allows for adding new
records. For this to work, the combo's Limit To List property needs to be
set to Yes so this event will fire

Private Sub cboActivity_NotInList(NewData As String, Response As Integer)
Dim rst As Recordset

If MsgBox(NewData & " Is Not In The Attribute Table " & vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes Then
Me.cboActivity = Me.cboActivity.OldValue
'Add the new record with the key field.
CurrentDb.Execute ("INSERT INTO CISAttributeTable (ACTIVITY) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
Me.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "[Activity] = '" & NewData & "'"
Me.Bookmark = rst.Bookmark
Set rst = Nothing
Me.txtDescription.SetFocus
Response = acDataErrAdded
Else
Me.cboActivity.Undo
Response = acDataErrContinue
End If

End Sub

Klatuu,

How would i usually have a search then? The only way i could get it to work
was to unbound it and have it as a text box. I am sure there was an easier
way e.g. Bound by combo or something but every time i tried to type in the
search it said i was duplicating data.

Sprinks,

Where would i enter this? ONLoad?

It sounds like you are using the form's recordset to do the search, so when
nothing is found, it ends up at the last record. It is unusual to use a text
box for a search, but if you must, I would suggest you check for the value is
in the table before you start messing with the form's recordset. I would
suggest the Before Update of your search text box.

Dim rst As Recordset

Set rst = Me.RecordsetClone
rst.FindFirst "[SearchField] = '" & Me.txtSearchFor & "'"
If rst.NoMatch Then
MsgBox "Record Not Found"
Cancel = True
Else
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing.

Access 2003,

I currently have a form which is used to search for and update records. The
field from which i search is a text box and is unbound and the search is by
Request ID.

When i type in a search which is not in the list, instead of keeping the
blank record which isbegins with, it brings up the first record. Hence, the
agents, if they are not paying enough attentino will not realise that the
record is incorrect.

If my table is called Acceptance and search field called Combo24 (not a
combo box), what code would I need to right in order to stop this from
happening.

Matt

(Klatuu, our old convo did not finish, apologies)

 

[Guest]

Yes but if i turn it into a combo and it is unbound, it will not do the "type
ahead thing" as it is not bound to anything. Surely it would have to be bound
in order for this to happen?

Also, I have added a date field to the form which I need to appear in the
reports. It show today's date but i cant get it to write to the reports. Do
you understand where I am coming from?

How do I make it so that the date that the quote was issued on has the date
with it?

Matt

Typically, you would use an unbound Combo. The Combo control is ideally
suited for doing this because it has the Autoexpand property which, when set
to Yes, does the "type ahead" thing and allows the user to find the value
quickly without having to type in the entire value. It also has the Not In
List property which either prevents or allows users to enter values that are
not in the row source of the combo. This is usually used to allow adding new
records to a table.

The code I posted earlier for the text box will work for the combo as well.
Here is an example of the Not In List event that allows for adding new
records. For this to work, the combo's Limit To List property needs to be
set to Yes so this event will fire

Private Sub cboActivity_NotInList(NewData As String, Response As Integer)
Dim rst As Recordset

If MsgBox(NewData & " Is Not In The Attribute Table " & vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes Then
Me.cboActivity = Me.cboActivity.OldValue
'Add the new record with the key field.
CurrentDb.Execute ("INSERT INTO CISAttributeTable (ACTIVITY) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
Me.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "[Activity] = '" & NewData & "'"
Me.Bookmark = rst.Bookmark
Set rst = Nothing
Me.txtDescription.SetFocus
Response = acDataErrAdded
Else
Me.cboActivity.Undo
Response = acDataErrContinue
End If

End Sub

Klatuu,

How would i usually have a search then? The only way i could get it to work
was to unbound it and have it as a text box. I am sure there was an easier
way e.g. Bound by combo or something but every time i tried to type in the
search it said i was duplicating data.

Sprinks,

Where would i enter this? ONLoad?

It sounds like you are using the form's recordset to do the search, so when
nothing is found, it ends up at the last record. It is unusual to use a text
box for a search, but if you must, I would suggest you check for the value is
in the table before you start messing with the form's recordset. I would
suggest the Before Update of your search text box.

Dim rst As Recordset

Set rst = Me.RecordsetClone
rst.FindFirst "[SearchField] = '" & Me.txtSearchFor & "'"
If rst.NoMatch Then
MsgBox "Record Not Found"
Cancel = True
Else
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing.

:

Access 2003,

I currently have a form which is used to search for and update records. The
field from which i search is a text box and is unbound and the search is by
Request ID.

When i type in a search which is not in the list, instead of keeping the
blank record which isbegins with, it brings up the first record. Hence, the
agents, if they are not paying enough attentino will not realise that the
record is incorrect.

If my table is called Acceptance and search field called Combo24 (not a
combo box), what code would I need to right in order to stop this from
happening.

Matt

(Klatuu, our old convo did not finish, apologies)

 

[Guest]

It does the type ahead on the combo's row source. A combo has to have a row
source. It can be a table, query, an SQL statement if you choose Table/Query
as the row source type. You can also select a Value list which is a typed in
list of values, or a Field list, which will show all the fields in a table or
query.

The most common way to do this is with a query. If you want to look up
values in a specific field in your table and the values are unique, then you
can use something like this
SELECT FieldToSearch FROM TableToSearch;

Don't confuse a Control Source with a Row Source. The control source is
used for binding the combo to a field. A Row Source is what the combo
displays.

Yes but if i turn it into a combo and it is unbound, it will not do the "type
ahead thing" as it is not bound to anything. Surely it would have to be bound
in order for this to happen?

Also, I have added a date field to the form which I need to appear in the
reports. It show today's date but i cant get it to write to the reports. Do
you understand where I am coming from?

How do I make it so that the date that the quote was issued on has the date
with it?

Matt

Typically, you would use an unbound Combo. The Combo control is ideally
suited for doing this because it has the Autoexpand property which, when set
to Yes, does the "type ahead" thing and allows the user to find the value
quickly without having to type in the entire value. It also has the Not In
List property which either prevents or allows users to enter values that are
not in the row source of the combo. This is usually used to allow adding new
records to a table.

The code I posted earlier for the text box will work for the combo as well.
Here is an example of the Not In List event that allows for adding new
records. For this to work, the combo's Limit To List property needs to be
set to Yes so this event will fire

Private Sub cboActivity_NotInList(NewData As String, Response As Integer)
Dim rst As Recordset

If MsgBox(NewData & " Is Not In The Attribute Table " & vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes Then
Me.cboActivity = Me.cboActivity.OldValue
'Add the new record with the key field.
CurrentDb.Execute ("INSERT INTO CISAttributeTable (ACTIVITY) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
Me.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "[Activity] = '" & NewData & "'"
Me.Bookmark = rst.Bookmark
Set rst = Nothing
Me.txtDescription.SetFocus
Response = acDataErrAdded
Else
Me.cboActivity.Undo
Response = acDataErrContinue
End If

End Sub

Klatuu,

How would i usually have a search then? The only way i could get it to work
was to unbound it and have it as a text box. I am sure there was an easier
way e.g. Bound by combo or something but every time i tried to type in the
search it said i was duplicating data.

Sprinks,

Where would i enter this? ONLoad?

:

It sounds like you are using the form's recordset to do the search, so when
nothing is found, it ends up at the last record. It is unusual to use a text
box for a search, but if you must, I would suggest you check for the value is
in the table before you start messing with the form's recordset. I would
suggest the Before Update of your search text box.

Dim rst As Recordset

Set rst = Me.RecordsetClone
rst.FindFirst "[SearchField] = '" & Me.txtSearchFor & "'"
If rst.NoMatch Then
MsgBox "Record Not Found"
Cancel = True
Else
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing.

:

Access 2003,

I currently have a form which is used to search for and update records. The
field from which i search is a text box and is unbound and the search is by
Request ID.

When i type in a search which is not in the list, instead of keeping the
blank record which isbegins with, it brings up the first record. Hence, the
agents, if they are not paying enough attentino will not realise that the
record is incorrect.

If my table is called Acceptance and search field called Combo24 (not a
combo box), what code would I need to right in order to stop this from
happening.

Matt

(Klatuu, our old convo did not finish, apologies)

 

[Guest]

Its been a long day!

WOrrying how I can not suss that myself even at this late hour!

Did you understand what i meant about the date. I need current date in the
form which then updates the relevant query and then in turn the relevant
report. I can have a field which brings up the current date but i can't then
get this into the relevant query. If i make it bound it obviously comes up
blank as the record has a blank date.

Matt

It does the type ahead on the combo's row source. A combo has to have a row
source. It can be a table, query, an SQL statement if you choose Table/Query
as the row source type. You can also select a Value list which is a typed in
list of values, or a Field list, which will show all the fields in a table or
query.

The most common way to do this is with a query. If you want to look up
values in a specific field in your table and the values are unique, then you
can use something like this
SELECT FieldToSearch FROM TableToSearch;

Don't confuse a Control Source with a Row Source. The control source is
used for binding the combo to a field. A Row Source is what the combo
displays.

Yes but if i turn it into a combo and it is unbound, it will not do the "type
ahead thing" as it is not bound to anything. Surely it would have to be bound
in order for this to happen?

Also, I have added a date field to the form which I need to appear in the
reports. It show today's date but i cant get it to write to the reports. Do
you understand where I am coming from?

How do I make it so that the date that the quote was issued on has the date
with it?

Matt

Typically, you would use an unbound Combo. The Combo control is ideally
suited for doing this because it has the Autoexpand property which, when set
to Yes, does the "type ahead" thing and allows the user to find the value
quickly without having to type in the entire value. It also has the Not In
List property which either prevents or allows users to enter values that are
not in the row source of the combo. This is usually used to allow adding new
records to a table.

The code I posted earlier for the text box will work for the combo as well.
Here is an example of the Not In List event that allows for adding new
records. For this to work, the combo's Limit To List property needs to be
set to Yes so this event will fire

Private Sub cboActivity_NotInList(NewData As String, Response As Integer)
Dim rst As Recordset

If MsgBox(NewData & " Is Not In The Attribute Table " & vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes Then
Me.cboActivity = Me.cboActivity.OldValue
'Add the new record with the key field.
CurrentDb.Execute ("INSERT INTO CISAttributeTable (ACTIVITY) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
Me.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "[Activity] = '" & NewData & "'"
Me.Bookmark = rst.Bookmark
Set rst = Nothing
Me.txtDescription.SetFocus
Response = acDataErrAdded
Else
Me.cboActivity.Undo
Response = acDataErrContinue
End If

End Sub


:

Klatuu,

How would i usually have a search then? The only way i could get it to work
was to unbound it and have it as a text box. I am sure there was an easier
way e.g. Bound by combo or something but every time i tried to type in the
search it said i was duplicating data.

Sprinks,

Where would i enter this? ONLoad?

:

It sounds like you are using the form's recordset to do the search, so when
nothing is found, it ends up at the last record. It is unusual to use a text
box for a search, but if you must, I would suggest you check for the value is
in the table before you start messing with the form's recordset. I would
suggest the Before Update of your search text box.

Dim rst As Recordset

Set rst = Me.RecordsetClone
rst.FindFirst "[SearchField] = '" & Me.txtSearchFor & "'"
If rst.NoMatch Then
MsgBox "Record Not Found"
Cancel = True
Else
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing.

:

Access 2003,

I currently have a form which is used to search for and update records. The
field from which i search is a text box and is unbound and the search is by
Request ID.

When i type in a search which is not in the list, instead of keeping the
blank record which isbegins with, it brings up the first record. Hence, the
agents, if they are not paying enough attentino will not realise that the
record is incorrect.

If my table is called Acceptance and search field called Combo24 (not a
combo box), what code would I need to right in order to stop this from
happening.

Matt

(Klatuu, our old convo did not finish, apologies)

 

[Guest]

Now we are talking about two different things. I don't understand how the
RequestID you are looking up and how the Date relate.

The date issue is pretty straight foward. If you are going to always use
the current date, it can be in the query. If the user can change the date,
the control they put it in does not have to be bound. When you reference the
value of a control on a form in a query, the query doesn't know and doesn't
care whether it is bound. It only looks at the value. When referecing a
form control in a query, regardless of where the control is in the query, it
is simply
[Forms]![FormName]![ControName]

There are a couple of issues surrounding dates in queries. You may need to
use
"#" & [Forms]![FormName]![ControName] & "#" to tell the query it is a date.
Also, if you are doing something like
[SomeTableDateField] = "#" & [Forms]![FormName]![ControName] & "#" and the
date in the table was populated using Now(), which contains time as well as
date, and comparing it to a date only Date() value, it will almost never
match, because the time portions will be different.

Its been a long day!

WOrrying how I can not suss that myself even at this late hour!

Did you understand what i meant about the date. I need current date in the
form which then updates the relevant query and then in turn the relevant
report. I can have a field which brings up the current date but i can't then
get this into the relevant query. If i make it bound it obviously comes up
blank as the record has a blank date.

Matt

It does the type ahead on the combo's row source. A combo has to have a row
source. It can be a table, query, an SQL statement if you choose Table/Query
as the row source type. You can also select a Value list which is a typed in
list of values, or a Field list, which will show all the fields in a table or
query.

The most common way to do this is with a query. If you want to look up
values in a specific field in your table and the values are unique, then you
can use something like this
SELECT FieldToSearch FROM TableToSearch;

Don't confuse a Control Source with a Row Source. The control source is
used for binding the combo to a field. A Row Source is what the combo
displays.

Yes but if i turn it into a combo and it is unbound, it will not do the "type
ahead thing" as it is not bound to anything. Surely it would have to be bound
in order for this to happen?

Also, I have added a date field to the form which I need to appear in the
reports. It show today's date but i cant get it to write to the reports. Do
you understand where I am coming from?

How do I make it so that the date that the quote was issued on has the date
with it?

Matt

:

Typically, you would use an unbound Combo. The Combo control is ideally
suited for doing this because it has the Autoexpand property which, when set
to Yes, does the "type ahead" thing and allows the user to find the value
quickly without having to type in the entire value. It also has the Not In
List property which either prevents or allows users to enter values that are
not in the row source of the combo. This is usually used to allow adding new
records to a table.

The code I posted earlier for the text box will work for the combo as well.
Here is an example of the Not In List event that allows for adding new
records. For this to work, the combo's Limit To List property needs to be
set to Yes so this event will fire

Private Sub cboActivity_NotInList(NewData As String, Response As Integer)
Dim rst As Recordset

If MsgBox(NewData & " Is Not In The Attribute Table " & vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes Then
Me.cboActivity = Me.cboActivity.OldValue
'Add the new record with the key field.
CurrentDb.Execute ("INSERT INTO CISAttributeTable (ACTIVITY) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
Me.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "[Activity] = '" & NewData & "'"
Me.Bookmark = rst.Bookmark
Set rst = Nothing
Me.txtDescription.SetFocus
Response = acDataErrAdded
Else
Me.cboActivity.Undo
Response = acDataErrContinue
End If

End Sub


:

Klatuu,

How would i usually have a search then? The only way i could get it to work
was to unbound it and have it as a text box. I am sure there was an easier
way e.g. Bound by combo or something but every time i tried to type in the
search it said i was duplicating data.

Sprinks,

Where would i enter this? ONLoad?

:

It sounds like you are using the form's recordset to do the search, so when
nothing is found, it ends up at the last record. It is unusual to use a text
box for a search, but if you must, I would suggest you check for the value is
in the table before you start messing with the form's recordset. I would
suggest the Before Update of your search text box.

Dim rst As Recordset

Set rst = Me.RecordsetClone
rst.FindFirst "[SearchField] = '" & Me.txtSearchFor & "'"
If rst.NoMatch Then
MsgBox "Record Not Found"
Cancel = True
Else
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing.

:

Access 2003,

I currently have a form which is used to search for and update records. The
field from which i search is a text box and is unbound and the search is by
Request ID.

When i type in a search which is not in the list, instead of keeping the
blank record which isbegins with, it brings up the first record. Hence, the
agents, if they are not paying enough attentino will not realise that the
record is incorrect.

If my table is called Acceptance and search field called Combo24 (not a
combo box), what code would I need to right in order to stop this from
happening.

Matt

(Klatuu, our old convo did not finish, apologies)

 

[Guest]

It doesnt really like the date formula:

In SQL i have entered Acceptance.[Date] = ("#" & [Forms]![Acceptance]![Date]
& "#").

The first acceptance is the name of the table and the second is the form!

Matt

Now we are talking about two different things. I don't understand how the
RequestID you are looking up and how the Date relate.

The date issue is pretty straight foward. If you are going to always use
the current date, it can be in the query. If the user can change the date,
the control they put it in does not have to be bound. When you reference the
value of a control on a form in a query, the query doesn't know and doesn't
care whether it is bound. It only looks at the value. When referecing a
form control in a query, regardless of where the control is in the query, it
is simply
[Forms]![FormName]![ControName]

There are a couple of issues surrounding dates in queries. You may need to
use
"#" & [Forms]![FormName]![ControName] & "#" to tell the query it is a date.
Also, if you are doing something like
[SomeTableDateField] = "#" & [Forms]![FormName]![ControName] & "#" and the
date in the table was populated using Now(), which contains time as well as
date, and comparing it to a date only Date() value, it will almost never
match, because the time portions will be different.

Its been a long day!

WOrrying how I can not suss that myself even at this late hour!

Did you understand what i meant about the date. I need current date in the
form which then updates the relevant query and then in turn the relevant
report. I can have a field which brings up the current date but i can't then
get this into the relevant query. If i make it bound it obviously comes up
blank as the record has a blank date.

Matt

It does the type ahead on the combo's row source. A combo has to have a row
source. It can be a table, query, an SQL statement if you choose Table/Query
as the row source type. You can also select a Value list which is a typed in
list of values, or a Field list, which will show all the fields in a table or
query.

The most common way to do this is with a query. If you want to look up
values in a specific field in your table and the values are unique, then you
can use something like this
SELECT FieldToSearch FROM TableToSearch;

Don't confuse a Control Source with a Row Source. The control source is
used for binding the combo to a field. A Row Source is what the combo
displays.

:

Yes but if i turn it into a combo and it is unbound, it will not do the "type
ahead thing" as it is not bound to anything. Surely it would have to be bound
in order for this to happen?

Also, I have added a date field to the form which I need to appear in the
reports. It show today's date but i cant get it to write to the reports. Do
you understand where I am coming from?

How do I make it so that the date that the quote was issued on has the date
with it?

Matt

:

Typically, you would use an unbound Combo. The Combo control is ideally
suited for doing this because it has the Autoexpand property which, when set
to Yes, does the "type ahead" thing and allows the user to find the value
quickly without having to type in the entire value. It also has the Not In
List property which either prevents or allows users to enter values that are
not in the row source of the combo. This is usually used to allow adding new
records to a table.

The code I posted earlier for the text box will work for the combo as well.
Here is an example of the Not In List event that allows for adding new
records. For this to work, the combo's Limit To List property needs to be
set to Yes so this event will fire

Private Sub cboActivity_NotInList(NewData As String, Response As Integer)
Dim rst As Recordset

If MsgBox(NewData & " Is Not In The Attribute Table " & vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes Then
Me.cboActivity = Me.cboActivity.OldValue
'Add the new record with the key field.
CurrentDb.Execute ("INSERT INTO CISAttributeTable (ACTIVITY) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
Me.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "[Activity] = '" & NewData & "'"
Me.Bookmark = rst.Bookmark
Set rst = Nothing
Me.txtDescription.SetFocus
Response = acDataErrAdded
Else
Me.cboActivity.Undo
Response = acDataErrContinue
End If

End Sub


:

Klatuu,

How would i usually have a search then? The only way i could get it to work
was to unbound it and have it as a text box. I am sure there was an easier
way e.g. Bound by combo or something but every time i tried to type in the
search it said i was duplicating data.

Sprinks,

Where would i enter this? ONLoad?

:

It sounds like you are using the form's recordset to do the search, so when
nothing is found, it ends up at the last record. It is unusual to use a text
box for a search, but if you must, I would suggest you check for the value is
in the table before you start messing with the form's recordset. I would
suggest the Before Update of your search text box.

Dim rst As Recordset

Set rst = Me.RecordsetClone
rst.FindFirst "[SearchField] = '" & Me.txtSearchFor & "'"
If rst.NoMatch Then
MsgBox "Record Not Found"
Cancel = True
Else
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing.

:

Access 2003,

I currently have a form which is used to search for and update records. The
field from which i search is a text box and is unbound and the search is by
Request ID.

When i type in a search which is not in the list, instead of keeping the
blank record which isbegins with, it brings up the first record. Hence, the
agents, if they are not paying enough attentino will not realise that the
record is incorrect.

If my table is called Acceptance and search field called Combo24 (not a
combo box), what code would I need to right in order to stop this from
happening.

Matt

(Klatuu, our old convo did not finish, apologies)

 

[Guest]

Try it without the #
Acceptance.[Date] = ([Forms]![Acceptance]![Date])

Date is not a good name for a field or a control since it is an Access
reserved word. The brackets should remove any confusion, but I don't know
that even that is 100% reliable.

It doesnt really like the date formula:

In SQL i have entered Acceptance.[Date] = ("#" & [Forms]![Acceptance]![Date]
& "#").

The first acceptance is the name of the table and the second is the form!

Matt

Now we are talking about two different things. I don't understand how the
RequestID you are looking up and how the Date relate.

The date issue is pretty straight foward. If you are going to always use
the current date, it can be in the query. If the user can change the date,
the control they put it in does not have to be bound. When you reference the
value of a control on a form in a query, the query doesn't know and doesn't
care whether it is bound. It only looks at the value. When referecing a
form control in a query, regardless of where the control is in the query, it
is simply
[Forms]![FormName]![ControName]

There are a couple of issues surrounding dates in queries. You may need to
use
"#" & [Forms]![FormName]![ControName] & "#" to tell the query it is a date.
Also, if you are doing something like
[SomeTableDateField] = "#" & [Forms]![FormName]![ControName] & "#" and the
date in the table was populated using Now(), which contains time as well as
date, and comparing it to a date only Date() value, it will almost never
match, because the time portions will be different.

Its been a long day!

WOrrying how I can not suss that myself even at this late hour!

Did you understand what i meant about the date. I need current date in the
form which then updates the relevant query and then in turn the relevant
report. I can have a field which brings up the current date but i can't then
get this into the relevant query. If i make it bound it obviously comes up
blank as the record has a blank date.

Matt

:

It does the type ahead on the combo's row source. A combo has to have a row
source. It can be a table, query, an SQL statement if you choose Table/Query
as the row source type. You can also select a Value list which is a typed in
list of values, or a Field list, which will show all the fields in a table or
query.

The most common way to do this is with a query. If you want to look up
values in a specific field in your table and the values are unique, then you
can use something like this
SELECT FieldToSearch FROM TableToSearch;

Don't confuse a Control Source with a Row Source. The control source is
used for binding the combo to a field. A Row Source is what the combo
displays.

:

Yes but if i turn it into a combo and it is unbound, it will not do the "type
ahead thing" as it is not bound to anything. Surely it would have to be bound
in order for this to happen?

Also, I have added a date field to the form which I need to appear in the
reports. It show today's date but i cant get it to write to the reports. Do
you understand where I am coming from?

How do I make it so that the date that the quote was issued on has the date
with it?

Matt

:

Typically, you would use an unbound Combo. The Combo control is ideally
suited for doing this because it has the Autoexpand property which, when set
to Yes, does the "type ahead" thing and allows the user to find the value
quickly without having to type in the entire value. It also has the Not In
List property which either prevents or allows users to enter values that are
not in the row source of the combo. This is usually used to allow adding new
records to a table.

The code I posted earlier for the text box will work for the combo as well.
Here is an example of the Not In List event that allows for adding new
records. For this to work, the combo's Limit To List property needs to be
set to Yes so this event will fire

Private Sub cboActivity_NotInList(NewData As String, Response As Integer)
Dim rst As Recordset

If MsgBox(NewData & " Is Not In The Attribute Table " & vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes Then
Me.cboActivity = Me.cboActivity.OldValue
'Add the new record with the key field.
CurrentDb.Execute ("INSERT INTO CISAttributeTable (ACTIVITY) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
Me.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "[Activity] = '" & NewData & "'"
Me.Bookmark = rst.Bookmark
Set rst = Nothing
Me.txtDescription.SetFocus
Response = acDataErrAdded
Else
Me.cboActivity.Undo
Response = acDataErrContinue
End If

End Sub


:

Klatuu,

How would i usually have a search then? The only way i could get it to work
was to unbound it and have it as a text box. I am sure there was an easier
way e.g. Bound by combo or something but every time i tried to type in the
search it said i was duplicating data.

Sprinks,

Where would i enter this? ONLoad?

:

It sounds like you are using the form's recordset to do the search, so when
nothing is found, it ends up at the last record. It is unusual to use a text
box for a search, but if you must, I would suggest you check for the value is
in the table before you start messing with the form's recordset. I would
suggest the Before Update of your search text box.

Dim rst As Recordset

Set rst = Me.RecordsetClone
rst.FindFirst "[SearchField] = '" & Me.txtSearchFor & "'"
If rst.NoMatch Then
MsgBox "Record Not Found"
Cancel = True
Else
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing.

:

Access 2003,

I currently have a form which is used to search for and update records. The
field from which i search is a text box and is unbound and the search is by
Request ID.

When i type in a search which is not in the list, instead of keeping the
blank record which isbegins with, it brings up the first record. Hence, the
agents, if they are not paying enough attentino will not realise that the
record is incorrect.

If my table is called Acceptance and search field called Combo24 (not a
combo box), what code would I need to right in order to stop this from
happening.

Matt

(Klatuu, our old convo did not finish, apologies)

 

[Guest]

Have pretty much tried everything around that and I cant get it to work.

I called the text box Date Today and tried both formulas and to no avail.

In the SQL it read: Acceptance.[Date] = ("#" & [Forms]![Acceptance]![Date
Today] & "#") and then i tried without the #.

Cheers

Matt

Try it without the #
Acceptance.[Date] = ([Forms]![Acceptance]![Date])

Date is not a good name for a field or a control since it is an Access
reserved word. The brackets should remove any confusion, but I don't know
that even that is 100% reliable.

It doesnt really like the date formula:

In SQL i have entered Acceptance.[Date] = ("#" & [Forms]![Acceptance]![Date]
& "#").

The first acceptance is the name of the table and the second is the form!

Matt

Now we are talking about two different things. I don't understand how the
RequestID you are looking up and how the Date relate.

The date issue is pretty straight foward. If you are going to always use
the current date, it can be in the query. If the user can change the date,
the control they put it in does not have to be bound. When you reference the
value of a control on a form in a query, the query doesn't know and doesn't
care whether it is bound. It only looks at the value. When referecing a
form control in a query, regardless of where the control is in the query, it
is simply
[Forms]![FormName]![ControName]

There are a couple of issues surrounding dates in queries. You may need to
use
"#" & [Forms]![FormName]![ControName] & "#" to tell the query it is a date.
Also, if you are doing something like
[SomeTableDateField] = "#" & [Forms]![FormName]![ControName] & "#" and the
date in the table was populated using Now(), which contains time as well as
date, and comparing it to a date only Date() value, it will almost never
match, because the time portions will be different.

:

Its been a long day!

WOrrying how I can not suss that myself even at this late hour!

Did you understand what i meant about the date. I need current date in the
form which then updates the relevant query and then in turn the relevant
report. I can have a field which brings up the current date but i can't then
get this into the relevant query. If i make it bound it obviously comes up
blank as the record has a blank date.

Matt

:

It does the type ahead on the combo's row source. A combo has to have a row
source. It can be a table, query, an SQL statement if you choose Table/Query
as the row source type. You can also select a Value list which is a typed in
list of values, or a Field list, which will show all the fields in a table or
query.

The most common way to do this is with a query. If you want to look up
values in a specific field in your table and the values are unique, then you
can use something like this
SELECT FieldToSearch FROM TableToSearch;

Don't confuse a Control Source with a Row Source. The control source is
used for binding the combo to a field. A Row Source is what the combo
displays.

:

Yes but if i turn it into a combo and it is unbound, it will not do the "type
ahead thing" as it is not bound to anything. Surely it would have to be bound
in order for this to happen?

Also, I have added a date field to the form which I need to appear in the
reports. It show today's date but i cant get it to write to the reports. Do
you understand where I am coming from?

How do I make it so that the date that the quote was issued on has the date
with it?

Matt

:

Typically, you would use an unbound Combo. The Combo control is ideally
suited for doing this because it has the Autoexpand property which, when set
to Yes, does the "type ahead" thing and allows the user to find the value
quickly without having to type in the entire value. It also has the Not In
List property which either prevents or allows users to enter values that are
not in the row source of the combo. This is usually used to allow adding new
records to a table.

The code I posted earlier for the text box will work for the combo as well.
Here is an example of the Not In List event that allows for adding new
records. For this to work, the combo's Limit To List property needs to be
set to Yes so this event will fire

Private Sub cboActivity_NotInList(NewData As String, Response As Integer)
Dim rst As Recordset

If MsgBox(NewData & " Is Not In The Attribute Table " & vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes Then
Me.cboActivity = Me.cboActivity.OldValue
'Add the new record with the key field.
CurrentDb.Execute ("INSERT INTO CISAttributeTable (ACTIVITY) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
Me.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "[Activity] = '" & NewData & "'"
Me.Bookmark = rst.Bookmark
Set rst = Nothing
Me.txtDescription.SetFocus
Response = acDataErrAdded
Else
Me.cboActivity.Undo
Response = acDataErrContinue
End If

End Sub


:

Klatuu,

How would i usually have a search then? The only way i could get it to work
was to unbound it and have it as a text box. I am sure there was an easier
way e.g. Bound by combo or something but every time i tried to type in the
search it said i was duplicating data.

Sprinks,

Where would i enter this? ONLoad?

:

It sounds like you are using the form's recordset to do the search, so when
nothing is found, it ends up at the last record. It is unusual to use a text
box for a search, but if you must, I would suggest you check for the value is
in the table before you start messing with the form's recordset. I would
suggest the Before Update of your search text box.

Dim rst As Recordset

Set rst = Me.RecordsetClone
rst.FindFirst "[SearchField] = '" & Me.txtSearchFor & "'"
If rst.NoMatch Then
MsgBox "Record Not Found"
Cancel = True
Else
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing.

:

Access 2003,

I currently have a form which is used to search for and update records. The
field from which i search is a text box and is unbound and the search is by
Request ID.

When i type in a search which is not in the list, instead of keeping the
blank record which isbegins with, it brings up the first record. Hence, the
agents, if they are not paying enough attentino will not realise that the
record is incorrect.

If my table is called Acceptance and search field called Combo24 (not a
combo box), what code would I need to right in order to stop this from
happening.

Matt

(Klatuu, our old convo did not finish, apologies)

 

[Guest]

I put this in the Criteria row of the query builder in the holdate column and
it worked fine
[forms]![zjunk]![txtADate]

Have pretty much tried everything around that and I cant get it to work.

I called the text box Date Today and tried both formulas and to no avail.

In the SQL it read: Acceptance.[Date] = ("#" & [Forms]![Acceptance]![Date
Today] & "#") and then i tried without the #.

Cheers

Matt

Try it without the #
Acceptance.[Date] = ([Forms]![Acceptance]![Date])

Date is not a good name for a field or a control since it is an Access
reserved word. The brackets should remove any confusion, but I don't know
that even that is 100% reliable.

It doesnt really like the date formula:

In SQL i have entered Acceptance.[Date] = ("#" & [Forms]![Acceptance]![Date]
& "#").

The first acceptance is the name of the table and the second is the form!

Matt

:

Now we are talking about two different things. I don't understand how the
RequestID you are looking up and how the Date relate.

The date issue is pretty straight foward. If you are going to always use
the current date, it can be in the query. If the user can change the date,
the control they put it in does not have to be bound. When you reference the
value of a control on a form in a query, the query doesn't know and doesn't
care whether it is bound. It only looks at the value. When referecing a
form control in a query, regardless of where the control is in the query, it
is simply
[Forms]![FormName]![ControName]

There are a couple of issues surrounding dates in queries. You may need to
use
"#" & [Forms]![FormName]![ControName] & "#" to tell the query it is a date.
Also, if you are doing something like
[SomeTableDateField] = "#" & [Forms]![FormName]![ControName] & "#" and the
date in the table was populated using Now(), which contains time as well as
date, and comparing it to a date only Date() value, it will almost never
match, because the time portions will be different.

:

Its been a long day!

WOrrying how I can not suss that myself even at this late hour!

Did you understand what i meant about the date. I need current date in the
form which then updates the relevant query and then in turn the relevant
report. I can have a field which brings up the current date but i can't then
get this into the relevant query. If i make it bound it obviously comes up
blank as the record has a blank date.

Matt

:

It does the type ahead on the combo's row source. A combo has to have a row
source. It can be a table, query, an SQL statement if you choose Table/Query
as the row source type. You can also select a Value list which is a typed in
list of values, or a Field list, which will show all the fields in a table or
query.

The most common way to do this is with a query. If you want to look up
values in a specific field in your table and the values are unique, then you
can use something like this
SELECT FieldToSearch FROM TableToSearch;

Don't confuse a Control Source with a Row Source. The control source is
used for binding the combo to a field. A Row Source is what the combo
displays.

:

Yes but if i turn it into a combo and it is unbound, it will not do the "type
ahead thing" as it is not bound to anything. Surely it would have to be bound
in order for this to happen?

Also, I have added a date field to the form which I need to appear in the
reports. It show today's date but i cant get it to write to the reports. Do
you understand where I am coming from?

How do I make it so that the date that the quote was issued on has the date
with it?

Matt

:

Typically, you would use an unbound Combo. The Combo control is ideally
suited for doing this because it has the Autoexpand property which, when set
to Yes, does the "type ahead" thing and allows the user to find the value
quickly without having to type in the entire value. It also has the Not In
List property which either prevents or allows users to enter values that are
not in the row source of the combo. This is usually used to allow adding new
records to a table.

The code I posted earlier for the text box will work for the combo as well.
Here is an example of the Not In List event that allows for adding new
records. For this to work, the combo's Limit To List property needs to be
set to Yes so this event will fire

Private Sub cboActivity_NotInList(NewData As String, Response As Integer)
Dim rst As Recordset

If MsgBox(NewData & " Is Not In The Attribute Table " & vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes Then
Me.cboActivity = Me.cboActivity.OldValue
'Add the new record with the key field.
CurrentDb.Execute ("INSERT INTO CISAttributeTable (ACTIVITY) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
Me.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "[Activity] = '" & NewData & "'"
Me.Bookmark = rst.Bookmark
Set rst = Nothing
Me.txtDescription.SetFocus
Response = acDataErrAdded
Else
Me.cboActivity.Undo
Response = acDataErrContinue
End If

End Sub


:

Klatuu,

How would i usually have a search then? The only way i could get it to work
was to unbound it and have it as a text box. I am sure there was an easier
way e.g. Bound by combo or something but every time i tried to type in the
search it said i was duplicating data.

Sprinks,

Where would i enter this? ONLoad?

:

It sounds like you are using the form's recordset to do the search, so when
nothing is found, it ends up at the last record. It is unusual to use a text
box for a search, but if you must, I would suggest you check for the value is
in the table before you start messing with the form's recordset. I would
suggest the Before Update of your search text box.

Dim rst As Recordset

Set rst = Me.RecordsetClone
rst.FindFirst "[SearchField] = '" & Me.txtSearchFor & "'"
If rst.NoMatch Then
MsgBox "Record Not Found"
Cancel = True
Else
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing.

:

Access 2003,

I currently have a form which is used to search for and update records. The
field from which i search is a text box and is unbound and the search is by
Request ID.

When i type in a search which is not in the list, instead of keeping the
blank record which isbegins with, it brings up the first record. Hence, the
agents, if they are not paying enough attentino will not realise that the
record is incorrect.

If my table is called Acceptance and search field called Combo24 (not a
combo box), what code would I need to right in order to stop this from
happening.

Matt

(Klatuu, our old convo did not finish, apologies)

 

[Douglas J. Steele]

PMFJI. The fact that Matt's commenting about "at this late hour" implies to
me that he's probably located across the Atlantic from us. That means his
Short Date format may be set to something other than mm/dd/yyyy. If his
Short Date format is dd/mm/yyyy, that means the SQL is going to see
Acceptance.[Date] = #04/10/2006#, but Access will treat it as the 10th of
April, because that's what Access does.

Matt, try:

Acceptance.[Date] =
Format([Forms]![Acceptance]![DateToday],"\#mm\/dd\/yyyy\#")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I put this in the Criteria row of the query builder in the holdate column
and
it worked fine
[forms]![zjunk]![txtADate]

Have pretty much tried everything around that and I cant get it to work.

I called the text box Date Today and tried both formulas and to no avail.

In the SQL it read: Acceptance.[Date] = ("#" & [Forms]![Acceptance]![Date
Today] & "#") and then i tried without the #.

Cheers

Matt

Try it without the #
Acceptance.[Date] = ([Forms]![Acceptance]![Date])

Date is not a good name for a field or a control since it is an Access
reserved word. The brackets should remove any confusion, but I don't
know
that even that is 100% reliable.

:

It doesnt really like the date formula:

In SQL i have entered Acceptance.[Date] = ("#" &
[Forms]![Acceptance]![Date]
& "#").

The first acceptance is the name of the table and the second is the
form!

Matt

:

Now we are talking about two different things. I don't understand
how the
RequestID you are looking up and how the Date relate.

The date issue is pretty straight foward. If you are going to
always use
the current date, it can be in the query. If the user can change
the date,
the control they put it in does not have to be bound. When you
reference the
value of a control on a form in a query, the query doesn't know and
doesn't
care whether it is bound. It only looks at the value. When
referecing a
form control in a query, regardless of where the control is in the
query, it
is simply
[Forms]![FormName]![ControName]

There are a couple of issues surrounding dates in queries. You may
need to
use
"#" & [Forms]![FormName]![ControName] & "#" to tell the query it is
a date.
Also, if you are doing something like
[SomeTableDateField] = "#" & [Forms]![FormName]![ControName] & "#"
and the
date in the table was populated using Now(), which contains time as
well as
date, and comparing it to a date only Date() value, it will almost
never
match, because the time portions will be different.

:

Its been a long day!

WOrrying how I can not suss that myself even at this late hour!

Did you understand what i meant about the date. I need current
date in the
form which then updates the relevant query and then in turn the
relevant
report. I can have a field which brings up the current date but i
can't then
get this into the relevant query. If i make it bound it obviously
comes up
blank as the record has a blank date.

Matt

:

It does the type ahead on the combo's row source. A combo has
to have a row
source. It can be a table, query, an SQL statement if you
choose Table/Query
as the row source type. You can also select a Value list which
is a typed in
list of values, or a Field list, which will show all the fields
in a table or
query.

The most common way to do this is with a query. If you want to
look up
values in a specific field in your table and the values are
unique, then you
can use something like this
SELECT FieldToSearch FROM TableToSearch;

Don't confuse a Control Source with a Row Source. The control
source is
used for binding the combo to a field. A Row Source is what
the combo
displays.

:

Yes but if i turn it into a combo and it is unbound, it will
not do the "type
ahead thing" as it is not bound to anything. Surely it would
have to be bound
in order for this to happen?

Also, I have added a date field to the form which I need to
appear in the
reports. It show today's date but i cant get it to write to
the reports. Do
you understand where I am coming from?

How do I make it so that the date that the quote was issued
on has the date
with it?

Matt

:

Typically, you would use an unbound Combo. The Combo
control is ideally
suited for doing this because it has the Autoexpand
property which, when set
to Yes, does the "type ahead" thing and allows the user to
find the value
quickly without having to type in the entire value. It
also has the Not In
List property which either prevents or allows users to
enter values that are
not in the row source of the combo. This is usually used
to allow adding new
records to a table.

The code I posted earlier for the text box will work for
the combo as well.
Here is an example of the Not In List event that allows for
adding new
records. For this to work, the combo's Limit To List
property needs to be
set to Yes so this event will fire

Private Sub cboActivity_NotInList(NewData As String,
Response As Integer)
Dim rst As Recordset

If MsgBox(NewData & " Is Not In The Attribute Table " &
vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes
Then
Me.cboActivity = Me.cboActivity.OldValue
'Add the new record with the key field.
CurrentDb.Execute ("INSERT INTO CISAttributeTable
(ACTIVITY) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
Me.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "[Activity] = '" & NewData & "'"
Me.Bookmark = rst.Bookmark
Set rst = Nothing
Me.txtDescription.SetFocus
Response = acDataErrAdded
Else
Me.cboActivity.Undo
Response = acDataErrContinue
End If

End Sub


:

Klatuu,

How would i usually have a search then? The only way i
could get it to work
was to unbound it and have it as a text box. I am sure
there was an easier
way e.g. Bound by combo or something but every time i
tried to type in the
search it said i was duplicating data.

Sprinks,

Where would i enter this? ONLoad?

:

It sounds like you are using the form's recordset to do
the search, so when
nothing is found, it ends up at the last record. It is
unusual to use a text
box for a search, but if you must, I would suggest you
check for the value is
in the table before you start messing with the form's
recordset. I would
suggest the Before Update of your search text box.

Dim rst As Recordset

Set rst = Me.RecordsetClone
rst.FindFirst "[SearchField] = '" & Me.txtSearchFor
& "'"
If rst.NoMatch Then
MsgBox "Record Not Found"
Cancel = True
Else
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing.

:

Access 2003,

I currently have a form which is used to search for
and update records. The
field from which i search is a text box and is
unbound and the search is by
Request ID.

When i type in a search which is not in the list,
instead of keeping the
blank record which isbegins with, it brings up the
first record. Hence, the
agents, if they are not paying enough attentino will
not realise that the
record is incorrect.

If my table is called Acceptance and search field
called Combo24 (not a
combo box), what code would I need to right in order
to stop this from
happening.

Matt

(Klatuu, our old convo did not finish, apologies)

 

[Guest]

Good catch. I would not be surprised.

PMFJI. The fact that Matt's commenting about "at this late hour" implies to
me that he's probably located across the Atlantic from us. That means his
Short Date format may be set to something other than mm/dd/yyyy. If his
Short Date format is dd/mm/yyyy, that means the SQL is going to see
Acceptance.[Date] = #04/10/2006#, but Access will treat it as the 10th of
April, because that's what Access does.

Matt, try:

Acceptance.[Date] =
Format([Forms]![Acceptance]![DateToday],"\#mm\/dd\/yyyy\#")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I put this in the Criteria row of the query builder in the holdate column
and
it worked fine
[forms]![zjunk]![txtADate]

Have pretty much tried everything around that and I cant get it to work.

I called the text box Date Today and tried both formulas and to no avail.

In the SQL it read: Acceptance.[Date] = ("#" & [Forms]![Acceptance]![Date
Today] & "#") and then i tried without the #.

Cheers

Matt


:

Try it without the #
Acceptance.[Date] = ([Forms]![Acceptance]![Date])

Date is not a good name for a field or a control since it is an Access
reserved word. The brackets should remove any confusion, but I don't
know
that even that is 100% reliable.

:

It doesnt really like the date formula:

In SQL i have entered Acceptance.[Date] = ("#" &
[Forms]![Acceptance]![Date]
& "#").

The first acceptance is the name of the table and the second is the
form!

Matt

:

Now we are talking about two different things. I don't understand
how the
RequestID you are looking up and how the Date relate.

The date issue is pretty straight foward. If you are going to
always use
the current date, it can be in the query. If the user can change
the date,
the control they put it in does not have to be bound. When you
reference the
value of a control on a form in a query, the query doesn't know and
doesn't
care whether it is bound. It only looks at the value. When
referecing a
form control in a query, regardless of where the control is in the
query, it
is simply
[Forms]![FormName]![ControName]

There are a couple of issues surrounding dates in queries. You may
need to
use
"#" & [Forms]![FormName]![ControName] & "#" to tell the query it is
a date.
Also, if you are doing something like
[SomeTableDateField] = "#" & [Forms]![FormName]![ControName] & "#"
and the
date in the table was populated using Now(), which contains time as
well as
date, and comparing it to a date only Date() value, it will almost
never
match, because the time portions will be different.

:

Its been a long day!

WOrrying how I can not suss that myself even at this late hour!

Did you understand what i meant about the date. I need current
date in the
form which then updates the relevant query and then in turn the
relevant
report. I can have a field which brings up the current date but i
can't then
get this into the relevant query. If i make it bound it obviously
comes up
blank as the record has a blank date.

Matt

:

It does the type ahead on the combo's row source. A combo has
to have a row
source. It can be a table, query, an SQL statement if you
choose Table/Query
as the row source type. You can also select a Value list which
is a typed in
list of values, or a Field list, which will show all the fields
in a table or
query.

The most common way to do this is with a query. If you want to
look up
values in a specific field in your table and the values are
unique, then you
can use something like this
SELECT FieldToSearch FROM TableToSearch;

Don't confuse a Control Source with a Row Source. The control
source is
used for binding the combo to a field. A Row Source is what
the combo
displays.

:

Yes but if i turn it into a combo and it is unbound, it will
not do the "type
ahead thing" as it is not bound to anything. Surely it would
have to be bound
in order for this to happen?

Also, I have added a date field to the form which I need to
appear in the
reports. It show today's date but i cant get it to write to
the reports. Do
you understand where I am coming from?

How do I make it so that the date that the quote was issued
on has the date
with it?

Matt

:

Typically, you would use an unbound Combo. The Combo
control is ideally
suited for doing this because it has the Autoexpand
property which, when set
to Yes, does the "type ahead" thing and allows the user to
find the value
quickly without having to type in the entire value. It
also has the Not In
List property which either prevents or allows users to
enter values that are
not in the row source of the combo. This is usually used
to allow adding new
records to a table.

The code I posted earlier for the text box will work for
the combo as well.
Here is an example of the Not In List event that allows for
adding new
records. For this to work, the combo's Limit To List
property needs to be
set to Yes so this event will fire

Private Sub cboActivity_NotInList(NewData As String,
Response As Integer)
Dim rst As Recordset

If MsgBox(NewData & " Is Not In The Attribute Table " &
vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes
Then
Me.cboActivity = Me.cboActivity.OldValue
'Add the new record with the key field.
CurrentDb.Execute ("INSERT INTO CISAttributeTable
(ACTIVITY) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
Me.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "[Activity] = '" & NewData & "'"
Me.Bookmark = rst.Bookmark
Set rst = Nothing
Me.txtDescription.SetFocus
Response = acDataErrAdded
Else
Me.cboActivity.Undo
Response = acDataErrContinue
End If

End Sub


:

Klatuu,

How would i usually have a search then? The only way i
could get it to work
was to unbound it and have it as a text box. I am sure
there was an easier
way e.g. Bound by combo or something but every time i
tried to type in the
search it said i was duplicating data.

Sprinks,

Where would i enter this? ONLoad?

:

It sounds like you are using the form's recordset to do
the search, so when
nothing is found, it ends up at the last record. It is
unusual to use a text
box for a search, but if you must, I would suggest you
check for the value is
in the table before you start messing with the form's
recordset. I would
suggest the Before Update of your search text box.

Dim rst As Recordset

Set rst = Me.RecordsetClone
rst.FindFirst "[SearchField] = '" & Me.txtSearchFor
& "'"
If rst.NoMatch Then
MsgBox "Record Not Found"
Cancel = True
Else
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing.

:

Access 2003,

I currently have a form which is used to search for
and update records. The
field from which i search is a text box and is
unbound and the search is by
Request ID.

When i type in a search which is not in the list,
instead of keeping the
blank record which isbegins with, it brings up the
first record. Hence, the
agents, if they are not paying enough attentino will
not realise that the
record is incorrect.

If my table is called Acceptance and search field
called Combo24 (not a
combo box), what code would I need to right in order
to stop this from
happening.

Matt

(Klatuu, our old convo did not finish, apologies)

 

[Guest]

The problem is that it does not recognise the Forms!Acceptance!DateToday and
keeps asking me for a parameter.

The DateToday field is the text box with the default value set to =Date().
For some reason it wont recognise this.

ANy ideas?

Good catch. I would not be surprised.

PMFJI. The fact that Matt's commenting about "at this late hour" implies to
me that he's probably located across the Atlantic from us. That means his
Short Date format may be set to something other than mm/dd/yyyy. If his
Short Date format is dd/mm/yyyy, that means the SQL is going to see
Acceptance.[Date] = #04/10/2006#, but Access will treat it as the 10th of
April, because that's what Access does.

Matt, try:

Acceptance.[Date] =
Format([Forms]![Acceptance]![DateToday],"\#mm\/dd\/yyyy\#")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I put this in the Criteria row of the query builder in the holdate column
and
it worked fine
[forms]![zjunk]![txtADate]


:

Have pretty much tried everything around that and I cant get it to work.

I called the text box Date Today and tried both formulas and to no avail.

In the SQL it read: Acceptance.[Date] = ("#" & [Forms]![Acceptance]![Date
Today] & "#") and then i tried without the #.

Cheers

Matt


:

Try it without the #
Acceptance.[Date] = ([Forms]![Acceptance]![Date])

Date is not a good name for a field or a control since it is an Access
reserved word. The brackets should remove any confusion, but I don't
know
that even that is 100% reliable.

:

It doesnt really like the date formula:

In SQL i have entered Acceptance.[Date] = ("#" &
[Forms]![Acceptance]![Date]
& "#").

The first acceptance is the name of the table and the second is the
form!

Matt

:

Now we are talking about two different things. I don't understand
how the
RequestID you are looking up and how the Date relate.

The date issue is pretty straight foward. If you are going to
always use
the current date, it can be in the query. If the user can change
the date,
the control they put it in does not have to be bound. When you
reference the
value of a control on a form in a query, the query doesn't know and
doesn't
care whether it is bound. It only looks at the value. When
referecing a
form control in a query, regardless of where the control is in the
query, it
is simply
[Forms]![FormName]![ControName]

There are a couple of issues surrounding dates in queries. You may
need to
use
"#" & [Forms]![FormName]![ControName] & "#" to tell the query it is
a date.
Also, if you are doing something like
[SomeTableDateField] = "#" & [Forms]![FormName]![ControName] & "#"
and the
date in the table was populated using Now(), which contains time as
well as
date, and comparing it to a date only Date() value, it will almost
never
match, because the time portions will be different.

:

Its been a long day!

WOrrying how I can not suss that myself even at this late hour!

Did you understand what i meant about the date. I need current
date in the
form which then updates the relevant query and then in turn the
relevant
report. I can have a field which brings up the current date but i
can't then
get this into the relevant query. If i make it bound it obviously
comes up
blank as the record has a blank date.

Matt

:

It does the type ahead on the combo's row source. A combo has
to have a row
source. It can be a table, query, an SQL statement if you
choose Table/Query
as the row source type. You can also select a Value list which
is a typed in
list of values, or a Field list, which will show all the fields
in a table or
query.

The most common way to do this is with a query. If you want to
look up
values in a specific field in your table and the values are
unique, then you
can use something like this
SELECT FieldToSearch FROM TableToSearch;

Don't confuse a Control Source with a Row Source. The control
source is
used for binding the combo to a field. A Row Source is what
the combo
displays.

:

Yes but if i turn it into a combo and it is unbound, it will
not do the "type
ahead thing" as it is not bound to anything. Surely it would
have to be bound
in order for this to happen?

Also, I have added a date field to the form which I need to
appear in the
reports. It show today's date but i cant get it to write to
the reports. Do
you understand where I am coming from?

How do I make it so that the date that the quote was issued
on has the date
with it?

Matt

:

Typically, you would use an unbound Combo. The Combo
control is ideally
suited for doing this because it has the Autoexpand
property which, when set
to Yes, does the "type ahead" thing and allows the user to
find the value
quickly without having to type in the entire value. It
also has the Not In
List property which either prevents or allows users to
enter values that are
not in the row source of the combo. This is usually used
to allow adding new
records to a table.

The code I posted earlier for the text box will work for
the combo as well.
Here is an example of the Not In List event that allows for
adding new
records. For this to work, the combo's Limit To List
property needs to be
set to Yes so this event will fire

Private Sub cboActivity_NotInList(NewData As String,
Response As Integer)
Dim rst As Recordset

If MsgBox(NewData & " Is Not In The Attribute Table " &
vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes
Then
Me.cboActivity = Me.cboActivity.OldValue
'Add the new record with the key field.
CurrentDb.Execute ("INSERT INTO CISAttributeTable
(ACTIVITY) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
Me.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "[Activity] = '" & NewData & "'"
Me.Bookmark = rst.Bookmark
Set rst = Nothing
Me.txtDescription.SetFocus
Response = acDataErrAdded
Else
Me.cboActivity.Undo
Response = acDataErrContinue
End If

End Sub


:

Klatuu,

How would i usually have a search then? The only way i
could get it to work
was to unbound it and have it as a text box. I am sure
there was an easier
way e.g. Bound by combo or something but every time i
tried to type in the
search it said i was duplicating data.

Sprinks,

Where would i enter this? ONLoad?

:

It sounds like you are using the form's recordset to do
the search, so when
nothing is found, it ends up at the last record. It is
unusual to use a text
box for a search, but if you must, I would suggest you
check for the value is
in the table before you start messing with the form's
recordset. I would
suggest the Before Update of your search text box.

Dim rst As Recordset

Set rst = Me.RecordsetClone
rst.FindFirst "[SearchField] = '" & Me.txtSearchFor
& "'"
If rst.NoMatch Then
MsgBox "Record Not Found"
Cancel = True
Else
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing.

:

Access 2003,

I currently have a form which is used to search for
and update records. The
field from which i search is a text box and is
unbound and the search is by
Request ID.

When i type in a search which is not in the list,
instead of keeping the
blank record which isbegins with, it brings up the
first record. Hence, the
agents, if they are not paying enough attentino will
not realise that the
record is incorrect.

If my table is called Acceptance and search field
called Combo24 (not a
combo box), what code would I need to right in order
to stop this from
happening.

Matt

(Klatuu, our old convo did not finish, apologies)

 

[Douglas J. Steele]

Make sure you haven't mistyped the name of the form, or of the text box.

I assume that the form is open when you're running the query.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The problem is that it does not recognise the Forms!Acceptance!DateToday
and
keeps asking me for a parameter.

The DateToday field is the text box with the default value set to =Date().
For some reason it wont recognise this.

ANy ideas?

Good catch. I would not be surprised.

PMFJI. The fact that Matt's commenting about "at this late hour"
implies to
me that he's probably located across the Atlantic from us. That means
his
Short Date format may be set to something other than mm/dd/yyyy. If his
Short Date format is dd/mm/yyyy, that means the SQL is going to see
Acceptance.[Date] = #04/10/2006#, but Access will treat it as the 10th
of
April, because that's what Access does.

Matt, try:

Acceptance.[Date] =
Format([Forms]![Acceptance]![DateToday],"\#mm\/dd\/yyyy\#")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I put this in the Criteria row of the query builder in the holdate
column
and
it worked fine
[forms]![zjunk]![txtADate]


:

Have pretty much tried everything around that and I cant get it to
work.

I called the text box Date Today and tried both formulas and to no
avail.

In the SQL it read: Acceptance.[Date] = ("#" &
[Forms]![Acceptance]![Date
Today] & "#") and then i tried without the #.

Cheers

Matt


:

Try it without the #
Acceptance.[Date] = ([Forms]![Acceptance]![Date])

Date is not a good name for a field or a control since it is an
Access
reserved word. The brackets should remove any confusion, but I
don't
know
that even that is 100% reliable.

:

It doesnt really like the date formula:

In SQL i have entered Acceptance.[Date] = ("#" &
[Forms]![Acceptance]![Date]
& "#").

The first acceptance is the name of the table and the second is
the
form!

Matt

:

Now we are talking about two different things. I don't
understand
how the
RequestID you are looking up and how the Date relate.

The date issue is pretty straight foward. If you are going to
always use
the current date, it can be in the query. If the user can
change
the date,
the control they put it in does not have to be bound. When
you
reference the
value of a control on a form in a query, the query doesn't
know and
doesn't
care whether it is bound. It only looks at the value. When
referecing a
form control in a query, regardless of where the control is in
the
query, it
is simply
[Forms]![FormName]![ControName]

There are a couple of issues surrounding dates in queries.
You may
need to
use
"#" & [Forms]![FormName]![ControName] & "#" to tell the query
it is
a date.
Also, if you are doing something like
[SomeTableDateField] = "#" & [Forms]![FormName]![ControName] &
"#"
and the
date in the table was populated using Now(), which contains
time as
well as
date, and comparing it to a date only Date() value, it will
almost
never
match, because the time portions will be different.

:

Its been a long day!

WOrrying how I can not suss that myself even at this late
hour!

Did you understand what i meant about the date. I need
current
date in the
form which then updates the relevant query and then in turn
the
relevant
report. I can have a field which brings up the current date
but i
can't then
get this into the relevant query. If i make it bound it
obviously
comes up
blank as the record has a blank date.

Matt

:

It does the type ahead on the combo's row source. A combo
has
to have a row
source. It can be a table, query, an SQL statement if you
choose Table/Query
as the row source type. You can also select a Value list
which
is a typed in
list of values, or a Field list, which will show all the
fields
in a table or
query.

The most common way to do this is with a query. If you
want to
look up
values in a specific field in your table and the values
are
unique, then you
can use something like this
SELECT FieldToSearch FROM TableToSearch;

Don't confuse a Control Source with a Row Source. The
control
source is
used for binding the combo to a field. A Row Source is
what
the combo
displays.

:

Yes but if i turn it into a combo and it is unbound, it
will
not do the "type
ahead thing" as it is not bound to anything. Surely it
would
have to be bound
in order for this to happen?

Also, I have added a date field to the form which I need
to
appear in the
reports. It show today's date but i cant get it to write
to
the reports. Do
you understand where I am coming from?

How do I make it so that the date that the quote was
issued
on has the date
with it?

Matt

:

Typically, you would use an unbound Combo. The Combo
control is ideally
suited for doing this because it has the Autoexpand
property which, when set
to Yes, does the "type ahead" thing and allows the
user to
find the value
quickly without having to type in the entire value.
It
also has the Not In
List property which either prevents or allows users to
enter values that are
not in the row source of the combo. This is usually
used
to allow adding new
records to a table.

The code I posted earlier for the text box will work
for
the combo as well.
Here is an example of the Not In List event that
allows for
adding new
records. For this to work, the combo's Limit To List
property needs to be
set to Yes so this event will fire

Private Sub cboActivity_NotInList(NewData As String,
Response As Integer)
Dim rst As Recordset

If MsgBox(NewData & " Is Not In The Attribute
Table " &
vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") =
vbYes
Then
Me.cboActivity = Me.cboActivity.OldValue
'Add the new record with the key field.
CurrentDb.Execute ("INSERT INTO
CISAttributeTable
(ACTIVITY) " _
& "VALUES ('" & NewData & "');"),
dbFailOnError
Me.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "[Activity] = '" & NewData & "'"
Me.Bookmark = rst.Bookmark
Set rst = Nothing
Me.txtDescription.SetFocus
Response = acDataErrAdded
Else
Me.cboActivity.Undo
Response = acDataErrContinue
End If

End Sub


:

Klatuu,

How would i usually have a search then? The only way
i
could get it to work
was to unbound it and have it as a text box. I am
sure
there was an easier
way e.g. Bound by combo or something but every time
i
tried to type in the
search it said i was duplicating data.

Sprinks,

Where would i enter this? ONLoad?

:

It sounds like you are using the form's recordset
to do
the search, so when
nothing is found, it ends up at the last record.
It is
unusual to use a text
box for a search, but if you must, I would suggest
you
check for the value is
in the table before you start messing with the
form's
recordset. I would
suggest the Before Update of your search text box.

Dim rst As Recordset

Set rst = Me.RecordsetClone
rst.FindFirst "[SearchField] = '" &
Me.txtSearchFor
& "'"
If rst.NoMatch Then
MsgBox "Record Not Found"
Cancel = True
Else
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing.

:

Access 2003,

I currently have a form which is used to search
for
and update records. The
field from which i search is a text box and is
unbound and the search is by
Request ID.

When i type in a search which is not in the
list,
instead of keeping the
blank record which isbegins with, it brings up
the
first record. Hence, the
agents, if they are not paying enough attentino
will
not realise that the
record is incorrect.

If my table is called Acceptance and search
field
called Combo24 (not a
combo box), what code would I need to right in
order
to stop this from
happening.

Matt

(Klatuu, our old convo did not finish,
apologies)

 

[Guest]

When it is closed, the parameter appears and even when the form is open, the
date does not get updated. I do not awnt all records to have the date
contained. I only want the ones that have been had some form of entry in to
the form!

Also, for current records, how do I get the default value to be the current
user, rather than just for blank records. My form styarts with a blank recrod
with has the correct default, but then as soon as i select a recrod this
dissapears. Is it possible?

Matt

Make sure you haven't mistyped the name of the form, or of the text box.

I assume that the form is open when you're running the query.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The problem is that it does not recognise the Forms!Acceptance!DateToday
and
keeps asking me for a parameter.

The DateToday field is the text box with the default value set to =Date().
For some reason it wont recognise this.

ANy ideas?

Good catch. I would not be surprised.

:

PMFJI. The fact that Matt's commenting about "at this late hour"
implies to
me that he's probably located across the Atlantic from us. That means
his
Short Date format may be set to something other than mm/dd/yyyy. If his
Short Date format is dd/mm/yyyy, that means the SQL is going to see
Acceptance.[Date] = #04/10/2006#, but Access will treat it as the 10th
of
April, because that's what Access does.

Matt, try:

Acceptance.[Date] =
Format([Forms]![Acceptance]![DateToday],"\#mm\/dd\/yyyy\#")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I put this in the Criteria row of the query builder in the holdate
column
and
it worked fine
[forms]![zjunk]![txtADate]


:

Have pretty much tried everything around that and I cant get it to
work.

I called the text box Date Today and tried both formulas and to no
avail.

In the SQL it read: Acceptance.[Date] = ("#" &
[Forms]![Acceptance]![Date
Today] & "#") and then i tried without the #.

Cheers

Matt


:

Try it without the #
Acceptance.[Date] = ([Forms]![Acceptance]![Date])

Date is not a good name for a field or a control since it is an
Access
reserved word. The brackets should remove any confusion, but I
don't
know
that even that is 100% reliable.

:

It doesnt really like the date formula:

In SQL i have entered Acceptance.[Date] = ("#" &
[Forms]![Acceptance]![Date]
& "#").

The first acceptance is the name of the table and the second is
the
form!

Matt

:

Now we are talking about two different things. I don't
understand
how the
RequestID you are looking up and how the Date relate.

The date issue is pretty straight foward. If you are going to
always use
the current date, it can be in the query. If the user can
change
the date,
the control they put it in does not have to be bound. When
you
reference the
value of a control on a form in a query, the query doesn't
know and
doesn't
care whether it is bound. It only looks at the value. When
referecing a
form control in a query, regardless of where the control is in
the
query, it
is simply
[Forms]![FormName]![ControName]

There are a couple of issues surrounding dates in queries.
You may
need to
use
"#" & [Forms]![FormName]![ControName] & "#" to tell the query
it is
a date.
Also, if you are doing something like
[SomeTableDateField] = "#" & [Forms]![FormName]![ControName] &
"#"
and the
date in the table was populated using Now(), which contains
time as
well as
date, and comparing it to a date only Date() value, it will
almost
never
match, because the time portions will be different.

:

Its been a long day!

WOrrying how I can not suss that myself even at this late
hour!

Did you understand what i meant about the date. I need
current
date in the
form which then updates the relevant query and then in turn
the
relevant
report. I can have a field which brings up the current date
but i
can't then
get this into the relevant query. If i make it bound it
obviously
comes up
blank as the record has a blank date.

Matt

:

It does the type ahead on the combo's row source. A combo
has
to have a row
source. It can be a table, query, an SQL statement if you
choose Table/Query
as the row source type. You can also select a Value list
which
is a typed in
list of values, or a Field list, which will show all the
fields
in a table or
query.

The most common way to do this is with a query. If you
want to
look up
values in a specific field in your table and the values
are
unique, then you
can use something like this
SELECT FieldToSearch FROM TableToSearch;

Don't confuse a Control Source with a Row Source. The
control
source is
used for binding the combo to a field. A Row Source is
what
the combo
displays.

:

Yes but if i turn it into a combo and it is unbound, it
will
not do the "type
ahead thing" as it is not bound to anything. Surely it
would
have to be bound
in order for this to happen?

Also, I have added a date field to the form which I need
to
appear in the
reports. It show today's date but i cant get it to write
to
the reports. Do
you understand where I am coming from?

How do I make it so that the date that the quote was
issued
on has the date
with it?

Matt

:

Typically, you would use an unbound Combo. The Combo
control is ideally
suited for doing this because it has the Autoexpand
property which, when set
to Yes, does the "type ahead" thing and allows the
user to
find the value
quickly without having to type in the entire value.
It
also has the Not In
List property which either prevents or allows users to
enter values that are
not in the row source of the combo. This is usually
used
to allow adding new
records to a table.

The code I posted earlier for the text box will work
for
the combo as well.
Here is an example of the Not In List event that
allows for
adding new
records. For this to work, the combo's Limit To List
property needs to be
set to Yes so this event will fire

Private Sub cboActivity_NotInList(NewData As String,
Response As Integer)
Dim rst As Recordset

If MsgBox(NewData & " Is Not In The Attribute
Table " &
vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") =
vbYes
Then
Me.cboActivity = Me.cboActivity.OldValue
'Add the new record with the key field.
CurrentDb.Execute ("INSERT INTO
CISAttributeTable
(ACTIVITY) " _
& "VALUES ('" & NewData & "');"),
dbFailOnError
Me.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "[Activity] = '" & NewData & "'"
Me.Bookmark = rst.Bookmark
Set rst = Nothing
Me.txtDescription.SetFocus
Response = acDataErrAdded
Else
Me.cboActivity.Undo
Response = acDataErrContinue
End If

End Sub


:

Klatuu,

How would i usually have a search then? The only way
i
could get it to work
was to unbound it and have it as a text box. I am
sure
there was an easier
way e.g. Bound by combo or something but every time
i

 

[Douglas J. Steele]

The form must be open if you're trying to refer to it in the query.

I think you're going to have to step back and explain exactly what it is
you're trying to do. I don't understand what you mean about only wanting the
ones that have had some form of entry in the form. I also don't understand
what you mean about default values for current records. Default values only
apply to new records: once the record's been saved, the field either has a
value or it doesn't, but the default value no longer applies.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

When it is closed, the parameter appears and even when the form is open,
the
date does not get updated. I do not awnt all records to have the date
contained. I only want the ones that have been had some form of entry in
to
the form!

Also, for current records, how do I get the default value to be the
current
user, rather than just for blank records. My form styarts with a blank
recrod
with has the correct default, but then as soon as i select a recrod this
dissapears. Is it possible?

Matt

Make sure you haven't mistyped the name of the form, or of the text box.

I assume that the form is open when you're running the query.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The problem is that it does not recognise the
Forms!Acceptance!DateToday
and
keeps asking me for a parameter.

The DateToday field is the text box with the default value set to
=Date().
For some reason it wont recognise this.

ANy ideas?

:

Good catch. I would not be surprised.

:

PMFJI. The fact that Matt's commenting about "at this late hour"
implies to
me that he's probably located across the Atlantic from us. That
means
his
Short Date format may be set to something other than mm/dd/yyyy. If
his
Short Date format is dd/mm/yyyy, that means the SQL is going to see
Acceptance.[Date] = #04/10/2006#, but Access will treat it as the
10th
of
April, because that's what Access does.

Matt, try:

Acceptance.[Date] =
Format([Forms]![Acceptance]![DateToday],"\#mm\/dd\/yyyy\#")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I put this in the Criteria row of the query builder in the holdate
column
and
it worked fine
[forms]![zjunk]![txtADate]


:

Have pretty much tried everything around that and I cant get it
to
work.

I called the text box Date Today and tried both formulas and to
no
avail.

In the SQL it read: Acceptance.[Date] = ("#" &
[Forms]![Acceptance]![Date
Today] & "#") and then i tried without the #.

Cheers

Matt


:

Try it without the #
Acceptance.[Date] = ([Forms]![Acceptance]![Date])

Date is not a good name for a field or a control since it is an
Access
reserved word. The brackets should remove any confusion, but I
don't
know
that even that is 100% reliable.

:

It doesnt really like the date formula:

In SQL i have entered Acceptance.[Date] = ("#" &
[Forms]![Acceptance]![Date]
& "#").

The first acceptance is the name of the table and the second
is
the
form!

Matt

:

Now we are talking about two different things. I don't
understand
how the
RequestID you are looking up and how the Date relate.

The date issue is pretty straight foward. If you are going
to
always use
the current date, it can be in the query. If the user can
change
the date,
the control they put it in does not have to be bound. When
you
reference the
value of a control on a form in a query, the query doesn't
know and
doesn't
care whether it is bound. It only looks at the value.
When
referecing a
form control in a query, regardless of where the control is
in
the
query, it
is simply
[Forms]![FormName]![ControName]

There are a couple of issues surrounding dates in queries.
You may
need to
use
"#" & [Forms]![FormName]![ControName] & "#" to tell the
query
it is
a date.
Also, if you are doing something like
[SomeTableDateField] = "#" &
[Forms]![FormName]![ControName] &
"#"
and the
date in the table was populated using Now(), which contains
time as
well as
date, and comparing it to a date only Date() value, it will
almost
never
match, because the time portions will be different.

:

Its been a long day!

WOrrying how I can not suss that myself even at this late
hour!

Did you understand what i meant about the date. I need
current
date in the
form which then updates the relevant query and then in
turn
the
relevant
report. I can have a field which brings up the current
date
but i
can't then
get this into the relevant query. If i make it bound it
obviously
comes up
blank as the record has a blank date.

Matt

:

It does the type ahead on the combo's row source. A
combo
has
to have a row
source. It can be a table, query, an SQL statement if
you
choose Table/Query
as the row source type. You can also select a Value
list
which
is a typed in
list of values, or a Field list, which will show all
the
fields
in a table or
query.

The most common way to do this is with a query. If you
want to
look up
values in a specific field in your table and the values
are
unique, then you
can use something like this
SELECT FieldToSearch FROM TableToSearch;

Don't confuse a Control Source with a Row Source. The
control
source is
used for binding the combo to a field. A Row Source is
what
the combo
displays.

:

Yes but if i turn it into a combo and it is unbound,
it
will
not do the "type
ahead thing" as it is not bound to anything. Surely
it
would
have to be bound
in order for this to happen?

Also, I have added a date field to the form which I
need
to
appear in the
reports. It show today's date but i cant get it to
write
to
the reports. Do
you understand where I am coming from?

How do I make it so that the date that the quote was
issued
on has the date
with it?

Matt

:

Typically, you would use an unbound Combo. The
Combo
control is ideally
suited for doing this because it has the Autoexpand
property which, when set
to Yes, does the "type ahead" thing and allows the
user to
find the value
quickly without having to type in the entire value.
It
also has the Not In
List property which either prevents or allows users
to
enter values that are
not in the row source of the combo. This is
usually
used
to allow adding new
records to a table.

The code I posted earlier for the text box will
work
for
the combo as well.
Here is an example of the Not In List event that
allows for
adding new
records. For this to work, the combo's Limit To
List
property needs to be
set to Yes so this event will fire

Private Sub cboActivity_NotInList(NewData As
String,
Response As Integer)
Dim rst As Recordset

If MsgBox(NewData & " Is Not In The Attribute
Table " &
vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") =
vbYes
Then
Me.cboActivity = Me.cboActivity.OldValue
'Add the new record with the key field.
CurrentDb.Execute ("INSERT INTO
CISAttributeTable
(ACTIVITY) " _
& "VALUES ('" & NewData & "');"),
dbFailOnError
Me.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "[Activity] = '" & NewData &
"'"
Me.Bookmark = rst.Bookmark
Set rst = Nothing
Me.txtDescription.SetFocus
Response = acDataErrAdded
Else
Me.cboActivity.Undo
Response = acDataErrContinue
End If

End Sub


:

Klatuu,

How would i usually have a search then? The only
way
i
could get it to work
was to unbound it and have it as a text box. I am
sure
there was an easier
way e.g. Bound by combo or something but every
time
i

 

[Guest]

Yes, sorry i always have the problem of explaining things poorly.

Ignore, the default value, its my fault.

Right, within this form is a set of data, some fixed and some which is
entered when Agents call a particular client. I then have reports containing
whether a quote has been sent, rejected or not sent (ie call backs) and I
need a date field to go into only the not sent reports so i can pick up the
date of the calls.

How do i go about this. THe other two fields, have check boxes that bring up
the date within the form.

Matt

The form must be open if you're trying to refer to it in the query.

I think you're going to have to step back and explain exactly what it is
you're trying to do. I don't understand what you mean about only wanting the
ones that have had some form of entry in the form. I also don't understand
what you mean about default values for current records. Default values only
apply to new records: once the record's been saved, the field either has a
value or it doesn't, but the default value no longer applies.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

When it is closed, the parameter appears and even when the form is open,
the
date does not get updated. I do not awnt all records to have the date
contained. I only want the ones that have been had some form of entry in
to
the form!

Also, for current records, how do I get the default value to be the
current
user, rather than just for blank records. My form styarts with a blank
recrod
with has the correct default, but then as soon as i select a recrod this
dissapears. Is it possible?

Matt

Make sure you haven't mistyped the name of the form, or of the text box.

I assume that the form is open when you're running the query.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


The problem is that it does not recognise the
Forms!Acceptance!DateToday
and
keeps asking me for a parameter.

The DateToday field is the text box with the default value set to
=Date().
For some reason it wont recognise this.

ANy ideas?

:

Good catch. I would not be surprised.

:

PMFJI. The fact that Matt's commenting about "at this late hour"
implies to
me that he's probably located across the Atlantic from us. That
means
his
Short Date format may be set to something other than mm/dd/yyyy. If
his
Short Date format is dd/mm/yyyy, that means the SQL is going to see
Acceptance.[Date] = #04/10/2006#, but Access will treat it as the
10th
of
April, because that's what Access does.

Matt, try:

Acceptance.[Date] =
Format([Forms]![Acceptance]![DateToday],"\#mm\/dd\/yyyy\#")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I put this in the Criteria row of the query builder in the holdate
column
and
it worked fine
[forms]![zjunk]![txtADate]


:

Have pretty much tried everything around that and I cant get it
to
work.

I called the text box Date Today and tried both formulas and to
no
avail.

In the SQL it read: Acceptance.[Date] = ("#" &
[Forms]![Acceptance]![Date
Today] & "#") and then i tried without the #.

Cheers

Matt


:

Try it without the #
Acceptance.[Date] = ([Forms]![Acceptance]![Date])

Date is not a good name for a field or a control since it is an
Access
reserved word. The brackets should remove any confusion, but I
don't
know
that even that is 100% reliable.

:

It doesnt really like the date formula:

In SQL i have entered Acceptance.[Date] = ("#" &
[Forms]![Acceptance]![Date]
& "#").

The first acceptance is the name of the table and the second
is
the
form!

Matt

:

Now we are talking about two different things. I don't
understand
how the
RequestID you are looking up and how the Date relate.

The date issue is pretty straight foward. If you are going
to
always use
the current date, it can be in the query. If the user can
change
the date,
the control they put it in does not have to be bound. When
you
reference the
value of a control on a form in a query, the query doesn't
know and
doesn't
care whether it is bound. It only looks at the value.
When
referecing a
form control in a query, regardless of where the control is
in
the
query, it
is simply
[Forms]![FormName]![ControName]

There are a couple of issues surrounding dates in queries.
You may
need to
use
"#" & [Forms]![FormName]![ControName] & "#" to tell the
query
it is
a date.
Also, if you are doing something like
[SomeTableDateField] = "#" &
[Forms]![FormName]![ControName] &
"#"
and the
date in the table was populated using Now(), which contains
time as
well as
date, and comparing it to a date only Date() value, it will
almost
never
match, because the time portions will be different.

:

Its been a long day!

WOrrying how I can not suss that myself even at this late
hour!

Did you understand what i meant about the date. I need
current
date in the
form which then updates the relevant query and then in
turn
the
relevant
report. I can have a field which brings up the current
date
but i
can't then
get this into the relevant query. If i make it bound it
obviously
comes up
blank as the record has a blank date.

Matt

:

It does the type ahead on the combo's row source. A
combo
has
to have a row
source. It can be a table, query, an SQL statement if
you
choose Table/Query
as the row source type. You can also select a Value
list
which
is a typed in
list of values, or a Field list, which will show all
the
fields
in a table or
query.

The most common way to do this is with a query. If you
want to
look up
values in a specific field in your table and the values
are
unique, then you
can use something like this
SELECT FieldToSearch FROM TableToSearch;

Don't confuse a Control Source with a Row Source. The
control
source is
used for binding the combo to a field. A Row Source is
what
the combo
displays.

:

Yes but if i turn it into a combo and it is unbound,
it
will
not do the "type
ahead thing" as it is not bound to anything. Surely
it
would
have to be bound
in order for this to happen?

Also, I have added a date field to the form which I
need
to
appear in the
reports. It show today's date but i cant get it to
write
to
the reports. Do
you understand where I am coming from?

How do I make it so that the date that the quote was
issued
on has the date
with it?

Matt

:

Typically, you would use an unbound Combo. The
Combo
control is ideally
suited for doing this because it has the Autoexpand
property which, when set
to Yes, does the "type ahead" thing and allows the
user to
find the value
quickly without having to type in the entire value.
It
also has the Not In
List property which either prevents or allows users
to
enter values that are