If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
autofill in a form's control?
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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200612/1 |
#2
|
|||
|
|||
autofill in a form's control?
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 -- hth Al Campagna Candia Computer Consulting - Candia NH http://home.comcast.net/~cccsolutions "Slez via AccessMonster.com" u23064@uwe wrote in message news:6af4892c97eec@uwe... 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200612/1 |
#3
|
|||
|
|||
autofill in a form's control?
Slez via AccessMonster.com wrote: 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. |
#4
|
|||
|
|||
autofill in a form's control?
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 Al Campagna wrote: 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 -- Message posted via http://www.accessmonster.com |
#5
|
|||
|
|||
autofill in a form's control?
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 "Slez via AccessMonster.com" u23064@uwe wrote in message news:6af561e7a2572@uwe... 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 Al Campagna wrote: 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 -- Message posted via http://www.accessmonster.com |
#6
|
|||
|
|||
autofill in a form's control?
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 Al Campagna wrote: 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200612/1 |
#7
|
|||
|
|||
autofill in a form's control?
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 "Slez via AccessMonster.com" u23064@uwe wrote in message news:6afecbe06f262@uwe... 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 Al Campagna wrote: 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200612/1 |
#8
|
|||
|
|||
autofill in a form's control?
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 Al Campagna wrote: 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200612/1 |
#9
|
|||
|
|||
autofill in a form's control?
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 "Slez via AccessMonster.com" u23064@uwe wrote in message news:6b0056321105a@uwe... 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 Al Campagna wrote: 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200612/1 |
Thread Tools | |
Display Modes | |
|
|