I have a form that updates a table called BC - Issue Data Table. I am pulling in a carrier number from another table "BC - Carrier Data Table" using the DLookup function "on current" event in my form. The problem arises if the Dlookup doesn't find a matching record, it just adds the next record in my issue table. My issue table has an issue number that is an autonumber field. Each time this Dlookup doesn't find a match it adds a new issue that is blank. I want to run a Macro if the Dlookup is null instead of it erroneously creating a record. I have a field called issue_Index on the form which is carried over from another form previously filled out by the user. That is the criteria that is linked in both tables. Any ideas how to code if the below returns a null value to run a macro. Also should this be in "on current" or another form event location?
I wrote the code below in a module. The text box that this part of the code is getting its value from has a DLookup in its control source. The problem is that when the DLookup does not match the criteria it looks for, it returns Null. The code tries to assign Null to a variable of type currency and that returns an error. Is there anyway to convert the Null to 0?
All the function: Public Function CalculateDepositPlusVO() Dim vAgreedPrice As Currency Dim vtxtTotalVO As Currency Dim vtxtAgreedPricePlusVO As Currency
Forms![frmHouse]![qryHouse4].Form![AgreedPrice].SetFocus vAgreedPrice = Forms![frmHouse]![qryHouse4].Form![AgreedPrice].Text Forms![frmHouse]![qryHouse4].Form![txtTotalVO].SetFocus vtxtTotalVO = Forms![frmHouse]![qryHouse4].Form![txtTotalVO].Text vtxtAgreedPricePlusVO = vAgreedPrice + vtxtTotalVO Forms![frmHouse]![qryHouse4].Form![txtAgreedPricePlusVO].SetFocus Forms![frmHouse]![qryHouse4].Form![txtAgreedPricePlusVO].Text = vtxtAgreedPricePlusVO End Function
I am trying to run this code but getting a run time error 94 - null value - when it shouldn't be.I am thinking I have written the Dlookup incorrectly?
Code: Private Sub txtProductName_Click() Dim iProdType As Integer Dim ProductID As Integer iProdType = DLookup("ProductTypeID", "tblProduct", "ProductID" = Forms![frmBooking]![cboProductID].[Value]) Note ProductID in an integer
How can I diagnose this or what could the solution be?
The basic idea is that I need to insert a record into a table and then return the Key field so that I can use it to populate another table. This was working just fine for a while, then it stopped and naturally I can't think of anything I did to make it not work. I get an Invalid Use of Null error at the bolded step.
Dim SQL As String Dim SpecID As Long Dim VerifySpec As Variant ' Check for empty strings If Len(Me.txtNewPartNumber & vbNullString) = 0 Then Response = MsgBox("Part Number cannot be blank.", vbExclamation, "Missing Data") Exit Sub End If
I have a search from that has an option group, text fields, and a checkbox where the users selects a variety of option that generates a query. If the query return at least on record a split form (form on top/datasheet on bottom) is displayed and the form has an Edit and Close button. If the query returns no records the form is still displayed except the buttons are not visible.
If I knew the result of the query and then made a decision whether to open the form or release control back to the search box that would be great. To open the form I am using VBA with the DoCmd.OpenForm(,,"MyQuery","criteria") command.I thought about using the DLookup command and evaluate the return value for null to be used to control the program flow.
basically am creating a booking system, i have a add a room form. my form should check whether i already have a room number in my table, which works when the form is filled in. however when my form is null, then i press add new room button, i get this error rather than " please fill your form in"
Error: runtime error '3075' syntax error (missing operator) in query expression 'Room Number ='.
room number is a number field, integer but has primary key. i cant keep autonumber, as my requirement is to add new room number, but the roomnumber has to be unique.
here is the dlookup;
If DLookup("RoomNumber", "tblRooms", "RoomNumber = " & Forms!RoomPackages!txtRoomNumber) > 0 Then MsgBox "This number already exists." Else
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"
It might be an easy one but I just wasted the past hour deciphering through my code in order to solve the run-time error '94' that I'm getting when trying to execute the following code:
Code: Private Sub cmdUpdateDates_Click() '################################### 'This sub aims at combining the timesheet date and the start and end time into the fields [Start Time] and [End Time]. '################################### Dim intCounter As Integer intCounter = 0 Dim rs As ADODB.Recordset
A bit of a weird one, I've got a query and the criteria for showing records is that one particular field is null. However the query is showing records with the values in the field chosen for the Is Null.
Not sure why this is happening, has anyone come across this problem before?
I am having problems with setting up a set of combo boxes.
What I am trying to do is if combo Productline is empty then in combo PartNumber would show all products but if combo Productline has a value selected then in the combo partnumber would only be able to select the partnumbers in that productline.
Hi, I have some problem with assigmnet with date and string variable. what i wana do is get data from Forms textboxes into variable and then by insert query send to history table.
the problem occurs when there is blank textbox its says invalid use of null.
I have a query where data is first sorted by user input; first field's criteria: [fieldname], then by another field's criteria: Is Null.
I know there are records containing null values in the second field, as I have run a select query with the criteria: Like "*", to make sure they are null, and not zero-length-strings.
Hi. I have a macro (that runs a bat file) that I would like to run for the first time ,and only the first time that a form is run. The bat file will then copy over certain files the db needs. Is this possible?
I have a database and in order to get the correct values you need to run a few queries/make tables/delete tables ect. I was wondering if there was a way to code something so that on command click button it would run through all the neccessary steps so people dont have to manually do this.
Here's a sample of the database that I'm working on. I'm trying to help teachers take attendance of all of their students and so have the following tables.
tblAdmin - List of Teachers and their IDs tblAttendance - Courses, Student IDs, the Date and the Status (tardy, etc..) tblCourseInfo - List of Courses and the Teachers teaching the course tblEnrollment - List of the Courses and the students enrolled in them tblStudents - Student IDs and their names
The form that I want is a "Course Information Form" that will
(1) Display all the students enrolled in their course (2) Allow the teachers to take attendance everyday while keeping all the previous records
I've attached my sample database. I really appreciate ANY help any of you can suggest.
I am running a Macro that is running 3 query and saving inbetween each query. My issue is that when the macro runs I have to hit ok when the message boxes pop up saying "its about the run a Query" and then again when it says "your about to update the records". These two messages occur for each query and save. So I have to hit OK 6 times
Is there a way I can set the macro to NOT show these message boxes?
I usually search for the answers to my problems, but as im not sure what the problem is i havnt been able to do so here.
Basically i have a pretty simple database, it works fine in the UK. The company i have designed it for are moving to Budapest, i have just had an email this morning saying an error message pops up when the try using a form, i have attached the message. The macro just runs a query based on a table in the database asking the users to choose a record number edit. Im assuming it must be something to do with is being used in Budapest as it works fine here, but they are accessing it the same as they would do here, just from a different location.
I have a program thats work perfectly in some computers useing the network but when i try to open it in other computers i get an error and when i try to debug the error it highlit the line that i am calling the micro that open the mainform in it, i hope some one have an idea for what is going on with the program
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!!
I have inserted two combo box in the form with lookup in the table. Now I want to select values in these boxes and filter the records having those values on click of a command button placed on the form.
How can it be done without using VBA? Is it possible with use of macro?
I've managed to successfully setup my first Access database.
I have imported data from Microsoft Excel into an Access Database and Table within that (EmptyHomesTable).
The data relates to empty properties and every month at work we receive a list of empty homes in the month. Every month, this new data will be imported into a TemporaryImportTable.
I am then running an update query to compare the data in the EmptyHomesTable with TemporaryImportTable and 'close' those which are no longer empty (i.e. update their status in the EmptyHomesTable if the account reference number doesn’t match).
I am then running an append query to compare the data in the EmptyHomesTable with the TemporaryImportTable and add any new empty properties (i.e. add those which aren’t in the EmptyHomesTable by looking at the account reference numbers and adding them if they don’t match).
This gives us a working database of empty properties but doesn't delete those which are no longer empty (rather they are marked as closed).
What I'm trying to do is to run a Macro to automate all of this on a monthly basis.
Macro is as follows:
1. Delete Query to delete the data in the TemporaryImportTable but keep the table structure; 2. TransferSpreadsheet to import the latest Microsoft Excel file into TemporaryImportTable; 3. Update Query to close properties which are no longer empty in EmptyHomesTable; 4. Append Query to import those new empty properties in EmptyHomesTable;
The macro almost runs fine but I have a couple of questions to help finish it:
a).I’ve run the macro to update the February list to the January list which works fine. Running the macro to update the January and February list (combined) is almost fine but I’m 2 entries out. I can’t manually check as we’re talking about 1,500 entries. Is there another way?
b). Is there any way for the TransferSpreadsheet query to ask at each time of running the macro for the location of the Excel spreadsheet or do I need to go into the macro every time and change the file location?
c). One of the fields in the table is empty date (i.e. the date the property became empty). Is there a quick way to filter the entries before a certain date (i.e. only show those empty before 30 September 2007 for example)?
I have a procedure which I undertake and wonder whether it can be automated in any way.
I have a field on a form for Purchases (frmPurchases) for a Purchase Order number. To get the order, I click on a command (cmdpo) which opens another form and clicking a command on this form (cmdgetpono) produces a unique Purchase Order number. I then manually copy the number given and paste it into the field on frmPurchases (PONo).
I have not used Macros before but cannot see that there are the options to achieve this. If someone could suggest the ones I should uses fro the list it would be appreciates.
Alternatively, is there another way of looking at this?
I have a button that runs a macro to delete records in 12 tables. I want to create a message box before that macro runs warning that you are deleting records in 12 tables and are your sure you want to run the macro.
I need a message box with an ok and cancel button. Where do I put the msgbox funtion in this code?
Thanks !!
This is my code:
Private Sub cmdRunDeleteMacro_Click() On Error GoTo Err_cmdRunDeleteMacro_Click
I have a macro written that when clicked on it opens a web browser. Is there a way this can then point to a specific website url? I could make the default url in the browser point to a url but each time i click on the macro button the url needs to be different. all of the urls sit in a table within the database.
