I have this code to update my combo list when NewData is entered. NewData is not updating in the cbo box. I am new and still learning. Please help.
Private Sub POC_NotInList(NewData As String, Response As Integer)
On Error GoTo cboPOC_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("POC " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, " POC")
If intAnswer = vbYes Then
strSQL = "INSERT INTO MARK20A([POC]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
MsgBox "The new POC has been added to the list." _
, vbInformation, "POC "
Response = acDataErrAdded
Else
MsgBox "Please choose a POC from the list." _
, vbInformation, " "
Response = acDataErrContinue
End If
I have a form that has combo boxes and text fields (as well as sub forms). There is also a button linked to some code that says'
Private Sub cmdQuote_Click() 'Creates quote date and prints quote Me.QuoteDate = Now() Me.cbAgentID.Requery DoCmd.OpenReport "Quote", acViewPreview, , "BookingID = " & Me.BookingID End Sub
When the button is pressed the QuoteDate field (it is bound) should be be populated, but unfortunately it is not. I have played with refresh and requery but cannot derive a solution.
Hi well baisically the title explains the dilemma... I have a combobox in which the user selects an item and this updates the subform depending on the selection. However when i make a selection i get the following error:
Rumtime error '2101':
The setting you entered isnt valid for this property.
I understand what its telling me, however i cant seemed to find within the code where i have gone wrong, so heres the code:
Private Sub Combo4_AfterUpdate() UpdateSubform
End Sub
Private Sub UpdateSubform() Dim strSQL As String Dim varWhere As Variant
varWhere = Null
strSQL = "SELECT * FROM Sub_qry_CostCentre"
If (Me.Combo4 > 0) Then varWhere = " WHERE (CostCentre = " & Me.Combo4 & " )" ' MsgBox (strSQL & varWhere)
Works great, but when I hit the number "3", (3 times in row) it will let me into the form. I want it to not let me in IF I don't know the password.
Where did I go wrong?
Private Sub Form_Load() Dim pw As Variant
If InputBox("What is the password?", "Password") = "1" Then Else MsgBox "Invalid Password", vbCritical, "Sorry Charlie" DoCmd.Close If InputBox("What is the password?", "Password") = "2" Then Else MsgBox "Invalid Password", vbCritical, "Sorry Charlie" DoCmd.Close End If End If
I protect my code from people being able to read it by setting a password on the code from Tools > Properties, selecting the Protection tab and entering a password, and clicking "Lock Project"
Is there a way to write code that will remove that Lock Project check and check it back on?
I've looked through the Application.SetOption command and it doesn't seem to be one of the choices. It would be very helpful if someone knew how to do this.
I have a form with a combo box. If the supplier(s) name is not in the combo box, it asks do you want to add that supplier address? If yes, a popup form displays, where the name and address are entered. THis updates the table. But the problem is the combobox doesn't refresh so the user isn't aware that it has updated.
I've tried updating the form, but that results in the question being asked again, do you want to add that supplier address.
Here is my code
Dim intNewCategory As Integer, intTruncateName As Integer, strTitle As String, intMsgDialog As Integer
' Display message box asking if user wants to add a new category. strTitle = "Shipper Database" intMsgDialog = vbYesNo + vbQuestion + vbDefaultButton1 intNewCategory = MsgBox("Do you want to add this Company and Address to the List?", intMsgDialog, strTitle)
If intNewCategory = vbYes Then ' Remove new name from CategoryID combo box so ' control can be requeried when user returns to form. 'DoCmd.RunCommand acCmdUndo
' Display message box and adjust length of value entered in ' CategoryID combo box. strTitle = "Name Too Long" intMsgDialog = vbOKOnly + vbExclamation If Len(NewData) > 50 Then intTruncateName = MsgBox("Name of the Supplier can be no longer than " _ & "50 characters. The name you entered will be truncated.", _ intMsgDialog, strTitle) NewData = Left(NewData, 50) End If
A sent a mate a DB a while ago and I want to update it for him... so I need to send him something that will automatically update the forms, queries etc... progamatically i.e without him having to do anything to it.
Ideally, I'd like to front end/back end the DB. But server infrastructure doesn't allow for this. 2 networks are operating and they don't talk cleanly. Essentially, a FE/BE DB will work on one of the networks but not the other. Therefore, the DB is one whole DB so that BE tables don't need refreshing.
The way I see it... I have 2 options. Or maybe there are other options?? 1. Export forms, queries etc... to the old DB or 2. Import the data tables into the new one.
At this stage I prefer option 2.
I have some idea how do this this but would appreciate some advice. Ideally, I'd like the user to click an update button to import tables, delete the old DB and transfer the new DB into the appropriate folder (and maybe rename the DB if it needs it??)
I want to make an update code without referring to the form
My update query reads in is sql as follows Dim SQL As String StrSQL = "UPDATE products SET products.new = [products].[grossprice]-[products].[grossprice]*28/100" I want to rewrite it in the following way : Dim Diff As String Diff = [products].[grossprice] - [products].[grossprice] SQL = "UPDATE products SET products.new = diff*28/100 "
However this effort fails since i have to refer to a form.Can i build a code without referring to a form?
I have a database with details about employees in it.
There is a field with yearly salary in it and another with an ID number. Recently these slaaries have been increased by a non-standard amount. I have another table with the emplyee ID number and their new salary. I need to update the slaary figure in the first table with the salary figure in the second table. Is there anyway of doing this?
I need to be able to update all the date records in a table using an update query. Any ideas of what i have to put in the 'update to' box to get all the dates to change from dd/mm/2005 to be dd/mm/2006
Hi there people...i'm doing a databae project for A2 level coursework and have encountered a problem..please help me if you can. I have a table of names. Also another table (actions) which contains several fields, but 2 of them are names, (1 being Proposer, and other being Actionee), are obtained from the names table. However, when altering for instance the spelling of one of the names in the names table it does not update the spelling in the actions table. i have tried using the relationship-update option but this doesn't work because the names appear more than once in the actions table. if it were to appear just the once in the actions table then i wouldn't have any problem. but it is a requirement that the 2 names fields are in the actions table. Any ideas of how to solve this?
can someone tell me how to make a form's data change when related data is changed in another form. E.g. If I change the price of a product in 'products' to £3.00, how do I make it automatically show that it is £3.00 in 'orders' just by typing in the product code?
I have a "subformB" wich is inside a "MainForm". When "MainForm" loads up (and therefore subformB loads as well), "subformB" does not show the results it is supposed to show until I click on it. It doesn't update itself 'till I click on it. Can anyone give me a hand on this?
I have used a make table query to create a table with text values. I need to convert these values to numerical data, for instance "Male" should become 1 and "Female" should become 0. I tried doing this in an update query, but had to use 2 separate queries. Don't know if a macro would work to update a table (there are no forms in this DB, it's all data fields). Same with code, I usually do this with forms. How can I update the raw data, which in some cases has multiple ifs, into the correct format? Thanks!
i have 4 tables in access and i need to be able to add information in one table and it to appear in the other 3 without me having to go in to each individual table and enter it. Is this possible?
I have two tables. One table is linked to a text file that is always update by the another program. I have another table the is same exact one but it does not get updated. I have access run these two tables to see the difference between. (Its basically and add/drop function) But after I run those two tables to see the difference, I want to update the nonlink file, so it is up to date with the "link" table. Thank you.
Good morning, I'm a newbie....and I inherited this db.
I have an existing table. I need to add two rows of information. When I add the rows, they are empty. I have tried running a query with all the fields from the table plus the two rows I need, but I loose 90+ records. What am I doing wrong???
I have two tables. One table is linked to a text file that is always update by the another program. I have another table the is same exact one but it does not get updated. I have access run these two tables to see the difference between. (Its basically and add/drop function) But after I run those two tables to see the difference, I want to update the nonlink file, so it is up to date with the "link" table. Thank you.
I have a table which contains dates in the format (mm/dd/yyyy) as well as other information, I am trying to get an ASP website to update the date with values chosen by the user. The problem is that when using the UPDATE command it returns an error, saying the syntax is wrong. Here is my SQL statement and the error message it produces, if anyone has got any ideas, please let me know -:
"UPDATE Users SET Name = '"& UpName &"', DOB = #"& AddMonth &"/"& AddDay &"/"& AddYear &"# WHERE ID = "& ID &" "
The error message I get in return is -:
Error Type: Microsoft OLE DB Provider for ODBC Drivers (0x80040E14) [Microsoft][ODBC Microsoft Access Driver] Syntax error in date in query expression '#//#'.
I have a 1000 records with 4 columns , now normalized. I can use set to update and set some of the records. Perhaps there is a method I am missing. Is there some script or stored procedure I can run in sql to speed this up.
I have 181 units to match 1000 records as well as business leads any hints would be appreciated. I am matching the DepartmentID in the department table back to the field on another table. Thanks in advance Regards Angel
I have a table (A) with three variables: ProjectID, SubprofileID, and Budget. A projectID may have several subprofileIDs. I need to update this table monthly. Each month, I will get a table (B) with exact format. I want to update table A using data from Table B. I used UPDATE..... Left JOIN. But there is a probelm. For example, if project XX is not in Table B, the budget of project XX will be empty in Table A after updating whcih originally has data. Also, if Table B has a new Project YY, it is not added into Table A.
I have a church database for our choir...we are usually the same group each year ... I have built a database logging all robes, hymnals, etc... I have a table with the contact person (contact), address information, sizes, and yearly dues paid (year)... I would like to just update the year from one year to the next without having to change 135 individually... is there someway to automate this or a function that I can use.... I still would like to have access to the 2006 files to see if someone is making partial payments and check their records (as well pastdue balances) but at the same time move/update to 2007.... is there an easier way to this...I am fairly new to Access and don't mind trying something...I have backups of my database so I can try anything. thanks in advance..
I hope you don't mind extra questions from me on Access.
First I am creating a query, than I am creating a report with it. I am making for example query on pending projects, bring it to the report and then printing out. next week status of some reports changes to "executed" but I am getting new projects that are pending. will they automatically appear in my query and my report as a result simultaneously or I have to run another query just to make sure that I got all recent updates that I made in my table?
SELECT (Int([age]/10)*10) AS MYG, Count([MYG]) AS Frequency FROM Data WHERE ((([Data].[Date of referal]) Between #1/1/2006# And #1/1/2007#) And (([Data].[Age])>10)) GROUP BY (Int([age]/10)*10);
the [MYG] field is basically an integer 10,20,30,40,50 etc I wish to change this field to 10 - 20, 20 - 30, 30 - 40 etc.
