Not Null, Msgbox, Ammend Record - Bringing It All Together For A Newbie
Mar 27, 2006
Hi all,
New to this, but found some helpful tips/code already that I've integrated into my project.
You may well be familar the issue tracker database available for download from Office Online, well I'm using this for a little project I'm running.
What I want to do is tie up a few snippets as follows:
1-On my form I have a button called "Close"
2-When I click the Close button I want to check two fields, "closure" and "fix" and ensure that they have content i.e. not null
3-If they have content then I want to alter the "Status" field of the displayed record from Open or Suspended to closed (these are the three options for this field)
4-If the "closure" and/or "fix" fields are empty then I want a msgbox to pop up promting the user to fill out the relevent field/s, otherwise mark the record as closed
So;
Click close button, check contents of two fields, pop up a message if either or both empty, enter details in empty field/s, click button again and as both fields are now complete,mark the records status field as closed.
I've got so far but can't quite tie it all together, is what I'm asking possible? What do you need from me to help answer the question?
Thanks very much for your help.
View Replies
ADVERTISEMENT
Jul 27, 2005
I have a table field where Null is not allowed, how do I use a user freindly message to tell the user to add data to this field. When a user tries to leave the field Null (ie by tabbing out), they get a message, "tried to assign NUl value to vaiarble which is not variant datatype.
What I want to appear is more like "Please enter a name" or some other custom message.
Any ideas - I have tried before anfd aftyer update events and vaidation rule on the form field of Not Null, but the underlying message always appears first.
Paul
View 3 Replies
View Related
Oct 28, 2014
I have a problem with my database I have a combo box that will search for my record. Actually its working I input the specific number it goes to the specific record. But I want, if there no existing record in my database it will display a Messagebox that "No record Found" I try to put a code in a macro builder in a after update property field but nothing happened.
Expression code that it will display the msgbox if there's no record found.
the given code from macro builder is attached. I try to have an if else statement but I dont know how to not equal that giver conditional expression.
View 10 Replies
View Related
Aug 19, 2013
I'm using this stardard piece of code on a button that deletes a record from a simple continuous contact form.
Code:
If MsgBox("Do You Want To Delete This Record?", vbQuestion + vbYesNo + vbDefaultButton2, "Delete?") = vbYes Then
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord
End If
If the form is called "CONTACTS" with a field called "Contact_Name", what code do I use to have the relevant Contact_Name appear in the MsgBox to be sure that I'm deleting the correct record ...... something like this .....
Do You Want To Delete The Record Called Paul Quinn ?
View 2 Replies
View Related
Mar 10, 2005
hi,
i have a form, which when opened, gives me a blank record. which is what i asked it to do in the OnOpen property. however, i also need a msg box to popup - but after the form has been opened. i tried placing both pieces of 'code' into the OnOpen property, however, the popup displays first, then the form opens.
so currently i have:
Private Sub Form_Load()
Select Case MsgBox("Please select the Registration Number from the drop down menu or type it in", vbOKOnly, "Select Registration Number")
End Select
End Sub
--------------------------------------------------------------------------
Private Sub Form_Open(Cancel As Integer)
DoCmd.GoToRecord , , acNewRec
End Sub
--------------------------------------------------------------------------
but it does the same thing. so, is there a way to make the msg box appear after the form opens with a blank record?
thank you in advance :D
View 3 Replies
View Related
Jun 18, 2015
I've got a form which holds data for employees, fname, lname, ..... and an entered date which defaults to now().
On the before update event, i have the following.
Code:
If DCount("*", "trainingdata", "[Empid]=" & Me!EmpID & _
" And [subjectid]=" & Me!SubjectID & _
" And [trainingdate]=#" & Me!TrainingDate & "#") > 0 Then
MsgBox ("This record already exists")
Cancel = True
Me.Undo
Me.SubjectID.SetFocus
End If
End Sub
With the above vba code, a msgbox pops up if the record combination already exists. What I would like to do is in the same msgbox have the "entered" date display and make the msgbox say something like.
This record already exists and was entered on 6/18/2015, [entered]...
View 5 Replies
View Related
Oct 30, 2014
I want a Messagebox to be appear if the selected item or if the input item in the combobox was not in the record.
I used this condition in combobox
="[asstnumbr] = " & "'" & [Screen].[ActiveControl] & "'"
but when I try this run code function in macro via IFF will become error
IIf([asstnumbr]<>"'" & [Screen].[ActiveControl] & "'",MsgBox("NO RECORD FOUND IN YOUR SEARCH"))
View 1 Replies
View Related
Oct 26, 2005
How can I show data by category? I have 2 tables
1) Categories, field 1 in Category Id, Field 2 is Category name
2) Contacts, Field 1 is Category name which is the relationship with the category name field in the categories table, the rest of the fields are adress, phone, fax ect, ect.
I would like to be able to view the contacts by category name, but I don't know how to do it. I have created a report but the report shows all the contacts, not by category.
What is the first step in accomplishing this? I am not very adept with queries so please I can really use some giudance. I have also been on the MSOffice traiing site but that didn't help me much. Of course I didn't know what I training to do :).
View 2 Replies
View Related
Nov 3, 2006
I'm trying to modify an existing database to make it easier and faster to search for homeowners. Currently, there is a search window that has two radio buttons, and a list box. one radio button is to search by address and another by last name. When you click one of these, it shows all the records in the list box. The problem is that 1. this is time consuming scrolling through over 300 records, and 2. more search options are needed, to include tag numbers of vehicles, and also phone numbers of residents. What Im trying to do is replace the radio buttons with a text box that will search all of the wanted fields, and produce a list of results in the list box. I'd rather not use a search button and just have the list box filter out as you type, but if a button must be used then thats fine. I've tried different ways, and searched all over the new for weeks, but cannot find the solution to my problem. Here is the current and original code. Thanks in advance for your help....Mike
Type of Search = Radio Buttons
By Search Type = List Box
HOMEOWNERS = table where all the info is located
GET = cmd button to open form with results selected in list box
---------
Private Sub Option35_GotFocus()
End Sub
Private Sub Option37_GotFocus()
End Sub
Private Sub Type_of_Search_AfterUpdate()
With CodeContextObject
If .[Type of Search] = 1 Then
.[By Search Type].RowSource = ""
.[Search Text].Caption = "Select the Last Name to Search for"
.[By Search Type].ColumnCount = 3
.[By Search Type].ColumnWidths = "1.5 in;1 in;0 in"
.[By Search Type].BoundColumn = 3
.[By Search Type].RowSource = "SELECT DISTINCTROW HOMEOWNERS.LastName, HOMEOWNERS.FirstName, HOMEOWNERS.Address FROM HOMEOWNERS WHERE ((Not (HOMEOWNERS.LastName) Is Null)) ORDER BY HOMEOWNERS.LastName, HOMEOWNERS.Address;"
ElseIf .[Type of Search] = 2 Then
.[By Search Type].RowSource = ""
.[Search Text].Caption = "Select the Address to Search for"
.[By Search Type].ColumnCount = 2
.[By Search Type].ColumnWidths = "1.5 in;1 in"
.[By Search Type].BoundColumn = 1
.[By Search Type].RowSource = "SELECT HOMEOWNERS.Address, HOMEOWNERS.LastName FROM HOMEOWNERS WHERE ((Not (HOMEOWNERS.Address) Is Null)) ORDER BY HOMEOWNERS.Address;"
End If
End With
End Sub
Private Sub Get_Click()
On Error GoTo Get_Click_Err
Dim Criteria As String
Dim MyRS As DAO.Recordset
Set MyRS = Forms![BASIC DATA].RecordsetClone
Criteria = "[Address] = """ & Me![By Search Type] & """"
MyRS.FindFirst Criteria
If Not MyRS.NoMatch Then
Forms![BASIC DATA].Bookmark = MyRS.Bookmark
End If
MyRS.close
Set MyRS = Nothing
DoCmd.close acForm, "Homeowners Search Dialog"
Get_Click_Exit:
Exit Sub
Get_Click_Err:
MsgBox Err.Description
Resume Get_Click_Exit
End Sub
Private Sub close_button_Click()
On Error GoTo Err_close_button_Click
DoCmd.close
Exit_close_button_Click:
Exit Sub
Err_close_button_Click:
MsgBox Err.Description
Resume Exit_close_button_Click
End Sub
Private Sub close_Click()
On Error GoTo Err_close_Click
DoCmd.close
Exit_close_Click:
Exit Sub
Err_close_Click:
MsgBox Err.Description
Resume Exit_close_Click
End Sub
View 1 Replies
View Related
Feb 22, 2006
I have an Excel sheet that I'd like to bring into an Access Form -- populate the Excel cells with data from textboxes on the Access Form and print it. I tried copy/pasting various things in, but it's pasting even text from a cell as an Excel object (let alone joined cells and images). Where can I find more information about this?
I found http://support.microsoft.com/?kbid=210288
But, when I put that code into the form for saving text from a text box to an Excel cell, it tells me that the word "Set" is a "Compile error: Invalid outside procedure."
Also, how do I refer to a joined cell? I've found that I can copy from a joined cell, paste into a host cell and select "Paste Link". This puts an absolute reference to the joined cell in the host cell (=$A$7, for instance) and when I change the joined cell the host cell is changed. Pasting =$A$7 seems to merely put the face value of "=$A$7 into the host cell.
View 1 Replies
View Related
Mar 16, 2006
:eek: I have 'Arrival date' and 'Number of Nights' fields and i'm trying to find all clients that are staying on a certain date.
I enter the date in a form and reference this in a query.
In the Arrival Date criteria i have:
<=[Forms]![frmFindDate]![txtFindDate] And >=([Forms]![frmFindDate]![txtFindDate]-[tblStays]![NoNights])
which i thought might do it, but i get a message saying 'You canceled the previous operation.':confused:
If anyone can understand what I'm on about could you please help, it would be much appreciated.
View 3 Replies
View Related
Jan 10, 2005
Quick Scenrio:
I have a current employee's table and Retired/Resigned table, when an employee either retires, resigns or gets fired, i want to be able by the click of a switch or check box of some sort to move that record to the retired table. Your input is greatly apprectiated....
Eddie..
View 4 Replies
View Related
Jun 22, 2015
I am trying to transfer daily data that I get from three different queries all into one Excel sheet. I take it that you have to make one over-arching query which I have made called Awaiting Base.
View 4 Replies
View Related
Apr 10, 2014
I have the attached code in a query. It should be bringing back just one row for each record, however, if I have anything in any column other than Call_NUmber_int it brings back multiples if that record.
View 9 Replies
View Related
Sep 5, 2005
Hi everybody,
My question may sound you stupid but i am a newbie and i think you can help me...
My problem is,
I have records that have the id of a stuff and the error codes of the stuff which are occured in production. Therefore not all of the fields in the record are non-zero. I want to find the non-zero field/fields in a record...
Any help will be greatly appreciated...
Thanks...
View 1 Replies
View Related
Feb 19, 2008
Hello Smart People!
This one is driving me nuts. After doing quite a bit of research, I cannot find the answer that I need, so I am posting...
For a new record on frm1 I am trying to make the label (lblTest) for a text box named txtTest a certain color if a value has not been filled in. For some reason, I cannot get the code to work and I am guessing it is due to my lack of understanding of our good old friend NULL. Here is what in both the after update event for txtTest and the on current event for the form...
If Me.txtTest = Null Then
Me.lblTest.ForeColor = 255
Me.lblTest.FontBold = True
Else
Me.lblTest.ForeColor = 0
Me.lblTest.FontBold = False
End If
Why isn't the field red when I create a new record? Why is it that if I fill something into the field, then delete it, that it doesn't get bolded in red?
Regards,
James C.
View 6 Replies
View Related
Feb 5, 2006
I'm hoping someone can help a noob with a simple problem:) I did a search but couldn't find the answer.
When a user clears a record and leaves it Null I would like to prompt them whether they would like to delete the record before the access error message about invalid use of Null comes up. If they don't want to delete the record then the code should undo the change. The problem I have is I can't seem to run my code without the access error coming up. I can't see where I can trap the error (err.Number 3398).
For example, this doesn't work:
Private Sub txtSalesItem_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_txtSalesItem_BeforeUpdate
Dim response as Integer
If IsNull(Me.txtSalesItem) Then
response = MsgBox("Do you want to delete this category?", vbExclamation + vbYesNo, "Delete Sales Item?")
If response = 6 Then
Runcommand acCmdDelete
else
RunCommand acCmdUndo
endif
End If
.
.
.
I know I can put in a validation rule, but I would like to go a bit further.
Does anyone have any suggestions?
View 2 Replies
View Related
Oct 14, 2015
Okay so I have a column called 'totalincome' on TblCashflow. There are 60 records of TotalIncome (represents 5 years or 60 months) per asset that I have on another table, TblLoanGeneral.
What I need to return is the LAST value from totalincome that is not null. For example, we may only have entries in the 60 records that go to row #35, or 50 or whatever. When I use the Last criteria it gives me the 60th record (in example would give me a 0), and when I use the Max criteria, it gives me the largest income value regardless of location(would be 50,000 in example). What I need is really where the "last" income amt is >1 (20) however, when you set last, it only considers row 60.In the example below I would want 20 to be returned.
ID
Row#
TotalIncome
1
1
1000
1
2
2500
1
3
50000
1
4
20
1
5
0
1
6
0
1
7
0
View 7 Replies
View Related
May 2, 2007
Using the sql profiler gives me no clue.
This is the insert query from the profiler.
exec sp_executesql N'INSERT INTO "ENVIS_GSD".."wat_springflow" ("result_dt","site_id","result_va","remark_tx") VALUES (@P1,@P2,@P3,@P4,@P5,@P6)', N'@P1 datetime,@P2 int,@P3 float,@P4 nvarchar(4),@P5 varchar(3),@P6 datetime', 'Feb 10 2009 12:00:00:000AM', 3000723, 8.000000000000000e-001, N'test'
The two records that I get the proplem on are not included here, but somehow they have been updated. I traced the access code, but I could see no reference to them that they were used for the update in either the form or any modules called.
Somehow up to a few weeks ago those two columns were updated.
Tracing the code and using the immediate window the user_name variable stores the login name corectly.
Someone mentioned "error 28" any ideas?
Thanks in advance
View 1 Replies
View Related
Sep 7, 2005
What I need to accomplish is an alert if a query finds any records. In other words, When the main page is loaded a query will run in the background. This query is looking for any records that need to amended within the next 30 days. What i would like to do is if the query finds any records that need amending an alert "form" or conditional formatting in a field with turn it red as an indicator that attention is needed.
My problem is how do i write a code to see if the record set from the query is greater than null???
any ideas would be greatly appreciated.
Ricky
View 2 Replies
View Related
May 11, 2015
Nutshell: My user DB has one menu with 4 tabs (2 for adding records, and 2 for searching/editing records). Since the form opens up in the acFormAdd mode, Access already creates a new record (blank) before they type anything. It is resulting in blank false new records that are getting added to the table and they are getting annoying. My audit table is full of "user X created new record at this time" when it's just a blank record.
There must be a way to prevent this using VBA, but I cannot find it.
I've seen ways to disable "add new record" buttons and such, but nothing that disables Access from being smarter than me and creating a new record before I decide to.
View 2 Replies
View Related
Sep 23, 2014
I've designed a DB in access which has a BackEnd and 2 FrontEnds (one person insert all the records and the others just keep inserting infos till the process is finished.The DB has 12 tables and we used it for about 6 months without having any trouble but recently (2 weeks ago) i've add 3 new tables and then related them to one table that already exist.
The DB was running smoothly for a week after the changes but last monday (09/15) the "Record is deleted" appeared. I've compacted and repaired an the following errors descriptions appeared:
ErrorCode: -1017
ErrorDescription: Record is deleted.
ErrorTable: tblFatura
ErrorCode: -1053
ErrorDescription: Index or primary key cannot contain a Null value.
ErrorTable: tblFatura
ErrorCode: -1630
ErrorDescription: You cannot add or change a record because a related record is required in table 'TblExpense'.
ErrorTable: tblFatura
I've restored the file via IT using the Backup2 days before the error occurred but after 30 minutes the same error appeared! I dont know if it is related to the new tables that i have add or no?
View 3 Replies
View Related
Jul 11, 2013
On a push of a button from a form, I want to locate specific records within the table and update a field to Null if it matches a name
Dim strSQL As String
strSQL = "UPDATE tblmaster SET Score = "" WHERE [AdvocateName] = 'Anna Maria'"
DoCmd.RunSQL (strSQL)
I'm certain I don't have the syntax correct between the Set to Null and WHERE clauses..
View 4 Replies
View Related
Oct 2, 2014
If a new record is created, insert date only if customerID is not null
Private Sub Form_Current()
If "CustomerID", "TblDietPlan" = <> 0 Then
If Me.NewRecord Then Me.MealDate = DMax("MealDate", "TblDietPlan")
End Sub
View 1 Replies
View Related
Dec 6, 2012
I am building a database to capture monthly statistics on a number of items. I want to ensure that users don't enter statistics for the same item for the same reporting period. I found the following instruction, but can't make it work:
It suggests that I create multiple primary keys in the table
When I do it, it comes back with an error: Index or primary key cannot contain a null value.
View 6 Replies
View Related
Aug 13, 2013
I've created some code that enables a user to copy an existing record to a new record, which looks like this:
Private Sub btnCopytoNewRecord_Click()
Dim Salutation As String
Dim First_Name As String
Dim Surname As String
'Copy fields to variables
Salutation = Me.Salutation
[Code] ....
This code is working fine, until it hits an empty field, and then I get an error message: Run time error '94':
Invalid use of NullIs there some code I can use to tell it to skip any null fields?
View 2 Replies
View Related
