autofill in a form's control?

[Slez via AccessMonster.com]

I have a form where users enter the name of newly received projects. It's
opens via a simple macro when they click a command button. It opens the form
and goes to the new record in the field ProjectName. Behind the scenes,
another field, ProjectID, which is an autonumber field, assigns a unique
number. The trouble is that we sometimes run into is that you could
mistakenly enter the same ProjectName twice because they would each have a
different ProjectID, and whoever is doing the entry would have no way of
knowing this.

It would be a nice feature if as users were entering, text would autofill,
like a combo box. This would clue them into the fact that they may be
duplicating an entry already in the database. I tried to change the form to
a combobox to achieve this, but as you type, it's actually taking you to that
specific record rather than remaining in the new record.

Is there any way around this?
Suggestions are appreciated!
Slez

 

[Al Campagna]

Slez,
I think you'd be better off just using the BeforeUpdate event of ProjectName
(which has Cancel ability), and assuming ProjectName is a bound field.

If Not IsNull(DLookup("[ProjectName]", "tblYourTable","ProjectName = " & ProjectName) Then
Beep
MsgBox "This is a Duplicate ProjectName"
ProjectName.Undo
Cancel = True
End If

 

[pietlinden]

I have a form where users enter the name of newly received projects. It's
opens via a simple macro when they click a command button. It opens the form
and goes to the new record in the field ProjectName. Behind the scenes,
another field, ProjectID, which is an autonumber field, assigns a unique
number. The trouble is that we sometimes run into is that you could
mistakenly enter the same ProjectName twice because they would each have a
different ProjectID, and whoever is doing the entry would have no way of
knowing this.

It would be a nice feature if as users were entering, text would autofill,
like a combo box. This would clue them into the fact that they may be
duplicating an entry already in the database. I tried to change the form to
a combobox to achieve this, but as you type, it's actually taking you to that
specific record rather than remaining in the new record.

Is there any way around this?
Suggestions are appreciated!
Slez

Just index the Project Name field so that it doesn't allow duplicates.

 

[Slez via AccessMonster.com]

Thanks for the reply! I didn't want to go the route of the other reply
because there could be a duplicate name. My main purpose is to just alert
the user that they are "potentially" duplicating a project.

I used your code below and entered "Project", which is my table name. It
also gave me an error message that I was missing a ), so I entered that at
the end of the first line. The code now reads:

If Not IsNull(DLookup("[ProjectName]", "Project","ProjectName = " &
ProjectName)) Then

When I tried to add a duplicate ProjectName, it gave me a Run-time error
'3075' - Syntax error (missing operator) in query expression 'ProjectName =
Test Project'.

What might be causing the error? Should I be adding brackets somewhere?
Thanks again for the help!
Slez

Slez,
I think you'd be better off just using the BeforeUpdate event of ProjectName
(which has Cancel ability), and assuming ProjectName is a bound field.

If Not IsNull(DLookup("[ProjectName]", "tblYourTable","ProjectName = " & ProjectName) Then
Beep
MsgBox "This is a Duplicate ProjectName"
ProjectName.Undo
Cancel = True
End If

I have a form where users enter the name of newly received projects. It's
opens via a simple macro when they click a command button. It opens the form

[quoted text clipped - 14 lines]

Suggestions are appreciated!
Slez

 

[Al Campagna]

Slez,
Yes, I did miss a Parens at the end of the DLookup. One of the hazards of email
coding.
------------
Yes, if the combo field on the form is named "Test Project" then it will have to be
bracketed.

If Not IsNull(DLookup("[ProjectName]", "Project","ProjectName = " &

[Test Project])) Then

This is the reason why names with spaces should be avoided. No space names do not
require bracketing.
Try TestProject or Test_Project or just bracket.
------------
You can always add a Yes No button to the BeforeUpdate MsgBox, that would allow the
user to duplicate a ProjectName if necessary.
Yes = Keep the dupe name.
No = Cancel=True and TestProject.Undo... to kill the entry and retry another name.

--
hth
Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

--
hth
Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Thanks for the reply! I didn't want to go the route of the other reply
because there could be a duplicate name. My main purpose is to just alert
the user that they are "potentially" duplicating a project.

I used your code below and entered "Project", which is my table name. It
also gave me an error message that I was missing a ), so I entered that at
the end of the first line. The code now reads:

If Not IsNull(DLookup("[ProjectName]", "Project","ProjectName = " &
ProjectName)) Then

When I tried to add a duplicate ProjectName, it gave me a Run-time error
'3075' - Syntax error (missing operator) in query expression 'ProjectName =
Test Project'.

What might be causing the error? Should I be adding brackets somewhere?
Thanks again for the help!
Slez

Slez,
I think you'd be better off just using the BeforeUpdate event of ProjectName
(which has Cancel ability), and assuming ProjectName is a bound field.

If Not IsNull(DLookup("[ProjectName]", "tblYourTable","ProjectName = " & ProjectName)
Then
Beep
MsgBox "This is a Duplicate ProjectName"
ProjectName.Undo
Cancel = True
End If

I have a form where users enter the name of newly received projects. It's
opens via a simple macro when they click a command button. It opens the form

[quoted text clipped - 14 lines]

Suggestions are appreciated!
Slez

 

[Slez via AccessMonster.com]

I tried bracketing, but that did not change the error received. I think
there may be some confusion though. The error message lists "Test Project"
which is the value in the field ProjectName. I do not use spaces in my field
names, but the values can contain spaces, otherwise the user would be having
to enter - StJudeHospital or TheSmithCompany, which wouldn't be right.

If you have any other ideas that may help the problem, I'll appreciate it!
Thanks for your help so far!
Slez

Slez,
Yes, I did miss a Parens at the end of the DLookup. One of the hazards of email
coding.
------------
Yes, if the combo field on the form is named "Test Project" then it will have to be
bracketed.

If Not IsNull(DLookup("[ProjectName]", "Project","ProjectName = " &

[Test Project])) Then

This is the reason why names with spaces should be avoided. No space names do not
require bracketing.
Try TestProject or Test_Project or just bracket.
------------
You can always add a Yes No button to the BeforeUpdate MsgBox, that would allow the
user to duplicate a ProjectName if necessary.
Yes = Keep the dupe name.
No = Cancel=True and TestProject.Undo... to kill the entry and retry another name.

Thanks for the reply! I didn't want to go the route of the other reply
because there could be a duplicate name. My main purpose is to just alert

[quoted text clipped - 31 lines]

 

[Al Campagna]

Slez,
Let's take a step back and regroup. I'm getting a bit confused about the details.

You wrote...

there may be some confusion though. The error message lists "Test Project"
**which is the value in the field ProjectName.**

Is ProjectName still a combo??
My solution does not involve a combo box...
Just a text control named "ProjectName", bound to the ProjectName field in tblProjects
(my example name/s)

When the user enters a ProjectName, and using the BeforeUpdate event, that entered
value is "looked up" against the ProjectName in the existing tblProjects table to see If
it's a duplicate or not.
If it is, the user is warned with a MsgBox, and with the Yes/No button on the MsgBox,
the user can allow that duplicate name (with a different ProjectID of course) to be
entered, or if not, the process is cancelled, and the ProjectName on the form is reset to
Null or it's original value.

So... given those conditions, this BeforeUpdate code should work to identify a dupe...

If Not IsNull(DLookup("[ProjectName]", "tblProject", "ProjectName = " &
[ProjectName]))
or
If Not IsNull(DLookup("[ProjectName]", "tblProject", "ProjectName =
Forms!frmYourFormName!ProjectName"))

If this doesn't work then post all the details (exact names and setup) about your
form, your form field, and your table.

*** Note: Spaces in data "values" is... of course... OK. But, we must know if any of the
form or control elements of the BeforeUpdate code contains spaces.
--
hth
Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

I tried bracketing, but that did not change the error received. I think
there may be some confusion though. The error message lists "Test Project"
which is the value in the field ProjectName. I do not use spaces in my field
names, but the values can contain spaces, otherwise the user would be having
to enter - StJudeHospital or TheSmithCompany, which wouldn't be right.

If you have any other ideas that may help the problem, I'll appreciate it!
Thanks for your help so far!
Slez

Slez,
Yes, I did miss a Parens at the end of the DLookup. One of the hazards of email
coding.
------------
Yes, if the combo field on the form is named "Test Project" then it will have to be
bracketed.

If Not IsNull(DLookup("[ProjectName]", "Project","ProjectName = " &

[Test Project])) Then

This is the reason why names with spaces should be avoided. No space names do not
require bracketing.
Try TestProject or Test_Project or just bracket.
------------
You can always add a Yes No button to the BeforeUpdate MsgBox, that would allow the
user to duplicate a ProjectName if necessary.
Yes = Keep the dupe name.
No = Cancel=True and TestProject.Undo... to kill the entry and retry another name.

Thanks for the reply! I didn't want to go the route of the other reply
because there could be a duplicate name. My main purpose is to just alert

[quoted text clipped - 31 lines]

Suggestions are appreciated!
Slez

 

[Slez via AccessMonster.com]

Al,
Your second option for code did it! (With the - Forms!frmYourFormName!
ProjectName) You were correct that it is not a combo box. I need to add the
YesNo button yet, but otherwise it works just as desired.

Thanks so much for your extended help with this!
Slez

Slez,
Let's take a step back and regroup. I'm getting a bit confused about the details.

You wrote...

there may be some confusion though. The error message lists "Test Project"
**which is the value in the field ProjectName.**

Is ProjectName still a combo??
My solution does not involve a combo box...
Just a text control named "ProjectName", bound to the ProjectName field in tblProjects
(my example name/s)

When the user enters a ProjectName, and using the BeforeUpdate event, that entered
value is "looked up" against the ProjectName in the existing tblProjects table to see If
it's a duplicate or not.
If it is, the user is warned with a MsgBox, and with the Yes/No button on the MsgBox,
the user can allow that duplicate name (with a different ProjectID of course) to be
entered, or if not, the process is cancelled, and the ProjectName on the form is reset to
Null or it's original value.

So... given those conditions, this BeforeUpdate code should work to identify a dupe...

If Not IsNull(DLookup("[ProjectName]", "tblProject", "ProjectName = " &
[ProjectName]))
or
If Not IsNull(DLookup("[ProjectName]", "tblProject", "ProjectName =
Forms!frmYourFormName!ProjectName"))

If this doesn't work then post all the details (exact names and setup) about your
form, your form field, and your table.

*** Note: Spaces in data "values" is... of course... OK. But, we must know if any of the
form or control elements of the BeforeUpdate code contains spaces.

I tried bracketing, but that did not change the error received. I think
there may be some confusion though. The error message lists "Test Project"

[quoted text clipped - 30 lines]

 

[Al Campagna]

Slez,
Totally my fault...
Since ProjectName is text the DLookup should have been...

If Not IsNull(DLookup("[ProjectName]", "tblProject", "ProjectName = '" & [ProjectName] &
"'"))

Here's the Where with spaces between the quotes (remove spaces) for clarity...
"ProjectName = ' " & [ProjectName] & " ' "))

Sorry for the mistake... glad I offered my old tried and true "full path" solution just
in case.

--
hth
Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Al,
Your second option for code did it! (With the - Forms!frmYourFormName!
ProjectName) You were correct that it is not a combo box. I need to add the
YesNo button yet, but otherwise it works just as desired.

Thanks so much for your extended help with this!
Slez

Slez,
Let's take a step back and regroup. I'm getting a bit confused about the details.

You wrote...

there may be some confusion though. The error message lists "Test Project"
**which is the value in the field ProjectName.**

Is ProjectName still a combo??
My solution does not involve a combo box...
Just a text control named "ProjectName", bound to the ProjectName field in
tblProjects
(my example name/s)

When the user enters a ProjectName, and using the BeforeUpdate event, that entered
value is "looked up" against the ProjectName in the existing tblProjects table to see If
it's a duplicate or not.
If it is, the user is warned with a MsgBox, and with the Yes/No button on the MsgBox,
the user can allow that duplicate name (with a different ProjectID of course) to be
entered, or if not, the process is cancelled, and the ProjectName on the form is reset
to
Null or it's original value.

So... given those conditions, this BeforeUpdate code should work to identify a
dupe...

If Not IsNull(DLookup("[ProjectName]", "tblProject", "ProjectName = " &
[ProjectName]))
or
If Not IsNull(DLookup("[ProjectName]", "tblProject", "ProjectName =
Forms!frmYourFormName!ProjectName"))

If this doesn't work then post all the details (exact names and setup) about your
form, your form field, and your table.

*** Note: Spaces in data "values" is... of course... OK. But, we must know if any of
the
form or control elements of the BeforeUpdate code contains spaces.

I tried bracketing, but that did not change the error received. I think
there may be some confusion though. The error message lists "Test Project"

[quoted text clipped - 30 lines]

Suggestions are appreciated!
Slez