MS Access - Macro To Handle NotInList And LimitToList Properties
Jun 3, 2005
:confused: I need to find a way using macros to accomplish this task: I am trying to set the LimitToList property as a YES, and enter data that is not in the list already, but instead of the NotInList property giving me the following error "The item you entered is not in the list. Please choose one from the contents of the drop down box", I want to branch to a macro entered at theNotInList property to branch to that will eliminate the error, and give me the opportunity to enter my own message with options for the user to enter the item into the list, or try to choose an item from the drop down box instead. I want to do this with a macro if possible. I have seen many VB solutions that purport to solve the issue, but to be honest, I am not very well versed in VB procedures. That's why I would like to do it with a macro. I am very competent with macro procedures! Please help ASAP. Thanks.
View Replies
ADVERTISEMENT
Sep 19, 2013
Using Access 2007:
how to get macro properties using vba? I've attempted something similar to the below to try and divine macro properties:
Code:
Sub MacroDiscovery()
Dim mcr As AccessObject
Dim prop As AccessObjectProperty
For Each mcr In CurrentProject.AllMacros
Debug.Print mcr.Name
For Each prop In mcr.Properties
Debug.Print prop.Name
Debug.Print prop.Value
Next
Next
End Sub
Nothing comes up except the macro names. In my database, I have an AutoExec macro that I have manually added a description to; "This macro auto-executes when the database is opened by virtue of it's name." I can view the description by right-clicking on the macro and choosing 'Object Properties'.
Background:I am attempting to make a universal Access Database documenter for the many, many homemade databases on our network. I'm only interested in certain pieces of information and I would like to make a report to view the information how I want to view it. Thus, I don't care for the built-in documenter--I want to do it my way.
View 3 Replies
View Related
Feb 7, 2005
I have a combo with the LimitToList set to yes so I can add new data to the underlying tbl. This works fine with one exception. When I enter the data I occasionally want to enter it in all Capitals but when I select Ok to add it to the tbl the first letter is a capital & all others change to small caps. I'm a little rusty on Access at the moment so I need a prompt pointing me to what I need to change to fix my problem. I'm thinking this is a formatting problem? :(
View 2 Replies
View Related
Nov 5, 2007
I'm responsible for an Access 2000 database which three people have open more or less constantly and another four open from time to time.
The database is on a P2P network and is split, with the back end in a single location. The network is wireless.
At the moment there are no problems with this setup but changes are being contemplated which will mean that eight people will have the database open constantly with another four using it occasionally.
For other reasons ( There were only three other wireless networks within range when we started, now there are eight), we are switching the network from wireless to cable. The back end will be transferred to a new PC with a powerful processor and loads of memory.
Does anyone have experience of allowing that many users to log on at the same time in a P2P network? And was the experience good or bad?
View 6 Replies
View Related
Nov 4, 2014
I have below table in SQL Server which i have linked to MS Access 2010.
Create table test
(
col1 int not null
col2 int not null
)
Insert into Test values (1,2)
Insert into Test values (1,'')
Now when i link the table to access database and open up the table i dont have any issues with that. I tried to copy the test table data from access table and paste it to another access table which is linked to sql server with same schema structure thats when it says "You tried to assign the NULL value to a variable that is not a variant data type"..Its treating the blanks as NULLS when copy/paste the records from one table in access to another.
View 5 Replies
View Related
Dec 8, 2006
Hi, I would like to change the properties of either the column heads in a combo-box or the column names for tables. I don't think there's any way to adjust the column head properties, and I'm doubtful there's a way to change the column caption properties.
Some of my column titles are long, and I'd like to be able to word wrap them essentially. Anyone know if this is possible? Thanks.
View 5 Replies
View Related
Sep 15, 2004
Hi everybody!
Is it possible to set the properties for an access database not to confirm the changes when running an update query. I have done it under tools/properties etc. but then I make it in access in general. I want it to apply on one singel access database.
Anyone?
eroness
View 2 Replies
View Related
Mar 19, 2007
I am experimenting with Access Project and recently set up the MSDE 2000 on my desktop. My question is how do I set up user level permission in it simular to the user level security in access. I read that the MSDE 2000 or SQL 2000 has betten security features than MS Access jet, yet I haven't found a way to look into the property of the MSDE. Do I need another application in order to view the operating property of the MSDE or its security feature?
View 4 Replies
View Related
Jul 10, 2006
Hi Guys,
Does anyone know how i can get an excel file properties from Access. Thanks.
View 1 Replies
View Related
Jul 10, 2006
Hi Guys,
Does anyone know how i can get an excel file properties from Access. Thanks.
View 1 Replies
View Related
Dec 10, 2007
Hi,
I'm trying to migrate an Access 2000 project to Access 2007. I want that the frontend doesn't look like Access. This means: no ribbons, no office button, no Quick Access Toolbar, no database panel. In 2K, this was rather simple, but in 2007 I only managed so far to get rid of the ribbon (by replacing it with a blank ribbon) and the navigation pane, but I can't figure out what to do to hide the Office button and the QAT.
Can anyone help me with this ?
View 1 Replies
View Related
Dec 10, 2007
Hi,
I'm trying to migrate an Access 2000 project to Access 2007. I want that the frontend doesn't look like Access. This means: no ribbons, no office button, no Quick Access Toolbar, no database panel. In 2K, this was rather simple, but in 2007 I only managed so far to get rid of the ribbon (by replacing it with a blank ribbon) and the navigation pane, but I can't figure out what to do to hide the Office button and the QAT.
Can anyone help me with this ?
View 3 Replies
View Related
Feb 2, 2014
I have a Form with embedded Subform, everything works well as far as moving through records. Scrolling through records on main table via Form brings up correct records of child table on Subform, so the link between the 2 tables is done properly.
I use this logic to lock/unlock a field in the subform.
Code:
Private Sub Form_Current()
If IsNull(Me.Parent!Spec2) Then
Me!Value2.BorderStyle = Transparent
Me!Value2.SpecialEffect = Flat
Me!Value2.BackStyle = Transparent
[Code] ...
I confirmed with debug that the logic does set the properties properly. They are updated once and that's that. Any further passes through the logic do not update on the form. They do pass through correctly, changes are just ignored.
It doesn't matter if I use the logic from the Form and refer down, or in the Subform and refer up, the Subform never gets updated. I tried Repaint, Refresh, Requery, nothing works. I cannot count the permutations I've tried.
View 9 Replies
View Related
May 18, 2005
I added the notinlist event to an unbound combo (I use access 2003) and used the ADO code but no matter what I try I can't get it working. Out of desperation I substituted the DAO code and "bingo" I have a working combo which adds any data I type in etc, no errors no problems. My question now is; Do I now stick with the old rule "if it ain't broke don't fix it" and continue using the DAO code or do I continue to pull my hair out (not much left now) trying to get the ADO code working?
View 2 Replies
View Related
Nov 21, 2005
This is the NotInList I have been using for some time (below). it works well but only updates one field. I need to update two fields now, City and ZipCode. Can I add data to two fields each time the notinlist is envoked?
On Error GoTo Err_ErrorHandler
Const Message1 = " Is Not In The Database."
Const Message2 = "Would you like to add it?"
Const Title = "Unknown entry..."
Const NL = vbCrLf & vbCrLf
Dim db As DAO.Database
Dim rs As DAO.Recordset
If MsgBox(NewData & Message1 & NL & Message2, vbQuestion + vbYesNo, Title) = vbYes Then
Set db = CurrentDb
Set rs = db.OpenRecordset("MyTbl or Qry")
With rs
.AddNew
.Fields("MyField") = NewData
.Update
.Close
End With
Response = acDataErrAdded
Else
Me.MyField.Undo
Response = acDataErrContinue
End If
Exit_ErrorHandler:
Set rs = Nothing
Set db = Nothing
Exit Sub
Err_ErrorHandler:
MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
Resume Exit_ErrorHandler
View 3 Replies
View Related
Mar 9, 2006
hey
Well I'm haveing some problems here.
When I type something in my combobox that aint in the list it doesnt go to the precedure NotInList.
I need this for both of these comboboxes and I tried an altarnative way with rowsource but that aint working aswell
so here is the code
Private Sub cboReeks_AfterUpdate()
If cboReeks.Text <> cboReeks.RowSource Then
Call Foutmelding("U hebt een foutieve waarde ingevoerd")
cboReeks.Value = ""
End If
End Sub
Private Sub cboRonde_NotInList(NewData As String, Response As Integer)
Response = 0
Call Foutmelding("U hebt een foutieve waarde ingevoerd")
cboRonde.Value = ""
End Sub
Thanks Cobra
View 5 Replies
View Related
Dec 19, 2006
Hi all. This is the code that I have used in my NotInList event
Private Sub Suppliers_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_Suppliers_NotInList
'-- We may need to add another Product
Response = MsgBox("[" & NewData & "] is not yet a valid Supplier..." & vbCr & vbCr & _
"Would you like to add this Supplier to your DataBase?", vbYesNo)
If Response = vbYes Then
'-- Create a new Product record
Dim db As DAO.Database
Dim MySQL As String
Set db = CurrentDb()
MySQL = "Insert Into TableSupplier (Suppliers) " & _
"Values(""" & NewData & """)"
db.Execute MySQL, dbFailOnError
Set db = Nothing
Response = acDataErrAdded '-- will requery!!
Else
Response = acDataErrContinue
End If
Exit_Suppliers_NotInList:
On Error Resume Next
Set db = Nothing
Exit Sub
Err_Suppliers_NotInList:
MsgBox "Error No: " & Err.Number & vbCr & _
"Description: " & Err.Description
Resume Exit_Suppliers_NotInList
End Sub
The problem i'm having is that the code is not working. However, I have used the same code but modified to suit on another field elsewhere in the form which works fine. Any ideas????
View 4 Replies
View Related
Dec 8, 2013
I am trying to write a more complex macro that will start another macro at a preset time, however I am getting stopped at the first hurdle - getting a macro to run another macro.
Here is the code i am using at the moment, all I want to do currently is click the first button, then get the second macro to execute. But no luck, getting error 2157 "cannot find the procedure"
Code:
Private Sub Command3_Click()
MsgBox "1st macro running", vbExclamation, "Note"
Application.Run "teststart1"
' Application.OnTime TimeValue("19:55:00"), "teststart1"
[code]....
View 1 Replies
View Related
Apr 11, 2006
OKay so i have a combobox with 2 fields, City and CityCode. (i.e. Albany, ALBA) I want to be able to type in a new citycode or city so that when it's not in the list the user can add it to the list along with the field that they're not entering which would be city or citycode. Please help! thanks
View 1 Replies
View Related
May 13, 2007
Hi All, I'am using the following code to update a table (tbl_CDT) on a NotInList event of a cboBox (CDT) I keep getting a SQL syntax error message, can anyone help resolve the code please
Private Sub CDT_NotInList(NewData As String, Response As Integer)
On Error GoTo CDT_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The CDT " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add to the list now?" _
, vbQuestion + vbYesNo, "DentureBase")
If intAnswer = vbYes Then
strSQL = "INSERT INTO tbl_CDT's([CDT_Name]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new CDT has been added to the list." _
, vbInformation, "DentureBase"
Response = acDataErrAdded
Else
MsgBox "Please choose a CDT from the list." _
, vbInformation, "DentureBase"
Response = acDataErrContinue
End If
CDT_NotInList_Exit:
Exit Sub
CDT_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume CDT_NotInList_Exit
End Sub
Thanks very much
View 9 Replies
View Related
Apr 10, 2005
I have a combo box which is used to find a record on the form. I have set LimitToList = Yes and put in code for the event NotInList.
My problem is that my code is working fine allowing me to produce my own message, but then the standard message appears as well saying "The text you entered is not an item in the list"
Has anyone any ideas please?
View 2 Replies
View Related
Jun 29, 2005
Looking for a way to supress this message box...
The text you entered isn't an item in the list.
Select an item from the list or enter text that matches one of the listed items.
... when the NotInList event fires.
Trying to just open an unbound modal form, pass a couple values to it, and set focus on a particular combobox for additional information to be added to my lookup table:
Private Sub cboPostalCode_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_ErrorHandler
Const Message1 = "The postal code you have entered is not currently on record."
Const Message2 = "Would you like to add it?"
Const Title = "Unknown Postal Code"
Const NL = vbCrLf & vbCrLf
If MsgBox(Message1 & NL & Message2, vbQuestion + vbYesNo, Title) = vbYes Then
DoCmd.OpenForm "frmPostalCode"
Forms!frmPostalCode!cboCountryID = Me.cboCountryID
Forms!frmPostalCode!txtPostalCode = NewData
Forms!frmPostalCode.SetFocus
Forms!frmPostalCode!txtCity.SetFocus
Else
Me.cboPostalCode.Undo
End If
Exit_ErrorHandler:
Exit Sub
Err_ErrorHandler:
MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
Resume Exit_ErrorHandler
End Sub
Any thoughts?
Regards,
~Chad
View 1 Replies
View Related
Oct 3, 2006
I have 2 Cascading Combo boxes setup and working correctly.cbo1 lists all agents and cbo2 lists the branches that relate to each agent.tbl_agents and tbl_branches are setup with a 1 to many relationship.I have used the code (explained here (http://www.fontstuff.com/access/acctut20.htm)) that allows the user to add a new agent if they are not in the list. This works correctly. Now I want to let the user add a new branch for the agent they have just added. The code appears to work correctly from the front end, but when you check tbl_branch, there is a new entry but it is missing the appropriate agentID foreign key (ie, it's not related to the new agent).Is there something that I'm missing? I assume this is possible, but I must be doing something wrong.Maybe I need to create a little pop-up form for data entry?any help appreciated.
View 7 Replies
View Related
Dec 3, 2007
Hi,
Task: to extract data from table 1 of a database (tied to form 1) to create a record in another table 2 of the same database (tied to form 2).
Besides, I need to make it simple to use for an end user.
On the form 2 I have a command button that activates macro. Macro makes a query to run and extract data from table 1 and append a table 2.
Now I want the user to see the record on the screen (form 2) that has just been created. For that purpose I add "go to last record" step in the macro.
Problem: that doesn't work. :) For some reason it brings back same record from the middle of the table which is not the last. And what is even more interesting is that it doesn't tie to the record ID on the bottom of the screen (access generated).
Challenge: the record ID field in the table 2 is a primary key auto-numbered field (i know this is not perfect but I am not the one who created the database) and some records have been deleted over time. Might that be connected to that or that is something else?
Thank you!!
View 1 Replies
View Related
Jul 28, 2005
OK here is what I have:
Table Name: Student
Fields: Auto (Primary Key)
MOI (number)
Name (Text)
I have 2 forms:
1) Student ,which contains student info I have created a combo box in this form for MOI, so whenever I type an MOI ID that have been previously entered I get the student Name, if it's not previously entered I used the NotInList Event, that asks the user if he wants to add the new value in the list if Yes, it will forward him to the second form
2) student_info, which is an entry form the user enters the name of the student and go back to the student form.
The following is the NotInList event code:
Private Sub Combo10_NotInList(NewData As String, Response As Integer)
Dim Result
Dim Msg As String
Dim CR As String
CR = Chr$(13)
' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub
' Ask the user if he or she wishes to add the new student.
Msg = "'" & NewData & "' is not in the list." & CR & CR
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
' If the user chose Yes, start the student_info form in data entry
' mode as a dialog form, passing the new MOI in
' NewData to the OpenForm method's OpenArgs argument. The
' OpenArgs argument is used in student_info form's Form_Load event
' procedure.
DoCmd.OpenForm "student_info", , , , acAdd, acDialog, NewData
End If
' Look for the student the user created in the student_info form.
Result = DLookup("[MOI]", "student", _
"[MOI]='" & NewData & "'")
If IsNull(Result) Then
' If the student was not created, set the Response argument
' to suppress an error message and undo changes.
Response = acDataErrContinue
' Display a customized message.
MsgBox "Please try again!"
Else
' If the customer was created, set the Response argument to
' indicate that new data is being added.
Response = acDataErrAdded
End If
End Sub
The code is working perfectly except for one problem:
When I'm in student_info form to enter the name of new student and I want to go back to the Student Form I have this error message:
data type mismatch in criteria expression
I figured maybe because I'm passing a number field and in the DLookup criteria it assigns the MOI (which is number) to NewData which is String as follow:
DLookup("[MOI]", "student", _
"[MOI]='" & NewData & "'")
Attach it the database for better understanding of my prolem.
Any help will be very much appreicated!
Thanks,
CS.
View 14 Replies
View Related
Aug 1, 2013
I have two tables: Products and Invoice. Product table is for creating product list which will have additional information about products for calculations latter. Invoice is table which contains information about invoices and bought products. Invoice table column Product is combo box which I created that user wouldn't type same product in 100 variations (list is from table product column product description).
One invoice can contain more than one product which is not in product. NotinList add notinlist products to list but only one product at time (at least I know only this way).
Is it possible to add multiple records with notinlist? what i would like to to is:
1. user adds new invoice and access doesn't bothers with error messages about item not in list
2. after invoice have been added, access checks if all product is in list and offers to add those ones which is not in list to add to list.
View 6 Replies
View Related
