What method(s) are available to detect when a user is at a new record? I really just want to change the .text property of a combo box when the user is creating a new record. If this sounds stupid, I can explain the specifics.
View RepliesHi
I wrote code that should validate a field when entering a new record and then if a condition is true, that new record should be cancelled and not entered into the table.
I managed to partially achieve this by writing the code below, but the new record does not get cancelled because the table will still create a PK for that record and leave the rest of the fields empty. I am using an autonumber for the PK that's why the table creates it automatically What I want to achieve is to cancel the creation of a new record at once, I don't want even PK created for that new record.
I used the CancelUpdate because I thought it would cancel the record creation, but it did not! When I read about it it said that I need to use it with either Edit or AddNew, (which i don't understand why!) but it still does not work.
Private Sub PlotNum_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_msg
Dim db As DAO.Database, rs As DAO.Recordset
Dim n As Integer, i As Integer
Dim vPlotNum As Integer
Dim vPhaseID As Integer
vPhaseID = Forms![frmHouse].Form![PhaseID]
vPlotNum = Forms![frmHouse].[qryHouse2].Form![PlotNum]
Set db = CurrentDb
Set rs = db.OpenRecordset("tblHouse")
rs.MoveLast
n = rs.RecordCount
rs.MoveFirst
If n > 0 Then
For i = 1 To n
If rs![PhaseID] = vPhaseID Then
If rs![PlotNum] = vPlotNum Then
rs.Edit
rs.CancelUpdate
MsgBox "This plot number already exist in this particular phase." & vbCrLf & "Please choose a different Plot Number"
Forms![frmHouse].qryHouse2.Form![PlotNum].Text = ""
End If
End If
rs.MoveNext
Next i
End If
rs.Close
db.Close
Set db = Nothing
Set rs = Nothing
Exit_Err_msg:
Exit Sub
Err_msg:
MsgBox Err.Description
Resume Exit_Err_msg
End Sub
Any suggestions will be very much appreciated.
Thanks.
B
hi all,
I am doing one project using access. I 've made two forms. In one form, lets say, it contains two common buttons. Both buttons will load the same second form and will add new record to the same table. When I click first button, it will load second form with all fields blank and one field of record will autofill one value,lets say "a" to the table.That field shouldn't be appeared in the second form,just want to fill automatically. Then the other fields of new records will be filled by the user input from the second form.
Also, when I click second button, it will do similarly but only the autofill value will be different from the first one.
Does anyone know how to make it?
If don't understand what I am saying, I can explain it again.
Any help will be grateful for me.
I try this code and it's not working
no record found
Code:
Private Sub openForm_but_Click()
Dim whr As String
whr = "get_date = #" & Me.gDate & "#"
DoCmd.OpenForm "searchResult_form", acNormal, "", whr, acFormPropertySettings, acDialog
End Sub
Access 2002 . Can I condition a field to 'locked' on just one record of a continuous form subfile, based on the contents of a 2nd field in same record?
View 14 Replies View RelatedGood morning
I am hoping some one can help me out with an SQL string I am having problems during to get working.
This is my code
Private Sub SearchRoleSeeking()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strFilterSQL As String
Dim txtBoxValue As String
Set db = CurrentDb
Set qdf = db.QueryDefs("qrySearch")
lstSearchResults = txtSearch1
If Me.txtSearch1.Value = "Temp" Then
txtBoxValue = 1
End If
If Me.txtSearch1.Value = "Perm" Then
txtBoxValue = 2
End If
If Me.txtSearch1.Value = "Temp or Perm" Then
txtBoxValue = 3
End If
strSQL = "SELECT tblPersonalInformation.[PersonalID],tblPersonalInformation.[Surname],tblPersonalInformation.[Forename],tblPersonalInformation.[DOB],tblPersonalInformation.[WantedRate],tblPersonalInformation.[WantedSalary],tblPersonalInformation.[Status],tblPersonalInformation.[RoleSeeking]" & _
"FROM tblPersonalInformation " & _
"WHERE tblPersonalInformation.RoleSeeking = ('txtboxvalue')"
qdf.SQL = strSQL
Me.lstSearchResults.RowSource = "qrysearch"
Set qdf = Nothing
Set db = Nothing
End Sub
let me try explain what I am trying to do.
I have a form where personal information is entered into. It has an option group that allows 3 choices.
I have made a form that I will use to search (the above code is from that.) What I am trying to do is if a person enters "Temp" into txtSearch1 then for the SQL statement to search "tblPersonalInformation.[RoleSeeking]" for the value 1.
If a person enters "PERM then for the statement to use 2 as the value for txtSearch1 and so on.
Can anyone help out with my task at hand.
Thanks everyone for your help.
Please don't criticize the naming, I've renamed to make the logic simpler to understand.
I am trying to run a query based on the value of a textbox. Right now if the text box is empty it shows all the records. This is what works:
SELECT A.ID, B.Weight
FROM A
LEFT JOIN B
ON A.ID = B.ID
WHERE B.Weight Like
IIf([forms]![Form]![txtBox] & ""="","*",[forms]![Form]![txtBox])
The problem is when tbl B is blank (and only when its blank), I also want to see all records where weight is null.
Some examples of what I've tried (I have tried variations of):
B.Weight Like IIf([forms]![Form]![txtBox] & ""="","* Or Is Null",[forms]![Form]![txtBox])
B.Weight IIf([forms]![Form]![txtBox] & ""="","Is Null or Like *",[forms]![Form]![txtBox])
I am not worried about the False condition, the problem is that Null is not being returned from inside an IIF().
When I set the WHERE statement to: WHERE B.Weight Is Null, it works. But if I try IIf([forms]![Form]![txtBox] & ""="",Is Null,[forms]![Form]![txtBox]). It doesnt work (Whether I put it in quotes or not).
Any help would be appreciated. Thank you.
hi i was wondering if anyone can tell me a condition or a command in which a user will be able to enter only 6 digit number. like 123456. if he tries to enter 234 the database should not accept. is this possible also? any help would be great.
Thanks
Please have a look at the following code (I am not good at VBA or SQL).
Me.[txtBox] = DLookup(“[SNum]”, “[Table1]”, “[RecID])
If Me.txtBox =>2 Then
Condition if true
Else
Condition if false
End If
I type a value in my [txtBox], a number. I want to evaluate if the typed value meets the condition or not. [SNum] is serially numbered and [RecID] is autonumber Primary Key. The above code is not working. How do I achieve this? Please help.
:o Please help... I am trying to get the following results. If the Qty is less than 99 bag 10 per, if the qty is between 100 and 999 bag 100 per and if the qty is greater than 999 bag 200 per.
Pkg Qty: IIf([QTY]<=99,"10 PER",(IIf([QTY]>99,"100 PER",IIf([QTY]>999,"200 PER"))))
I am only getting the results of "10 PER" AND "100 PER". How do I get the results I want?
i have a Yes/No checkbox to indicate whether a job has been done.. if it is done then i want it removed from the list.. so i think my query sql needs to be something like..
note..the bold bit is the bit i am questioning.
WHERE (((Booking_Main.Job_Date) Between Date() And 1+Date())) AND Booking_Confirm.Job_Done ="No"
or something like this??
i have created a search form. any word the user types it searches certain details in my enquiries.
in my form i have a list box that displays the enquiries sent by my query.
i have placed a Job_Booked (Yes/No field) checkbox in my enquiry table. this is because if the job is booked then i no longer want the record to appear in the enquiry list im sending to the form. ive tried this code, but even when i tick the job booked button, the enquiry is being displayed.. why is this
SELECT zEnquiries.Enquiry_Date, zEnquiries.Job_Date, zEnquiries.Job_Day, zEnquiries.Job_Time, zEnquiries.Enq_Forname, zEnquiries.Enq_Surname, zEnquiries.Pickup_Add, zEnquiries.Pickup_Post, zEnquiries.Dest_Add, zEnquiries.Dest_Post, zEnquiries.Quote, zEnquiries.Car, zEnquiries.Passengers, zEnquiries.Luggage, zEnquiries.Notes, zEnquiries.Job_Booked, *
FROM zEnquiries
WHERE (((zEnquiries.Enq_Forname) Like '*' & Forms!Enquiry_Search!Search2 & '*')) Or (((zEnquiries.Enq_Surname) Like '*' & Forms!Enquiry_Search!Search2 & '*')) Or (((zEnquiries.Job_Date) Like '*' & Forms!Enquiry_Search!Search2 & '*')) Or (((zEnquiries.Enq_Phone) Like '*' & Forms!Enquiry_Search!Search2 & '*')) Or (((zEnquiries.Dest_Add) Like '*' & Forms!Enquiry_Search!Search2 & '*')) Or (((zEnquiries.Dest_Post) Like '*' & Forms!Enquiry_Search!Search2 & '*')) Or (((zEnquiries.Pickup_Add) Like '*' & Forms!Enquiry_Search!Search2 & '*')) Or (((zEnquiries.Pickup_Post) Like '*' & Forms!Enquiry_Search!Search2 & '*')) And zEnquiries.Job_Booked=False
ORDER BY zEnquiries.Enquiry_Date DESC;
I can't get this expression in Query to work! Any suggestions?
Account Type: IIf([parent account name] like “*hosp*”,"Hosp",IIf([parent account name] like "*vet*”,"Vet",IIf([parent account name] like “*dds*”,"DENT”,IIf([parent account name] like "*dmd*”,"Dentist”,IIf([parent account name] like "*pharm*”,"Pharm”,0)))))
as you can see I am doing search and identify for a new field.
I get an error message "the expression you entered contains invalid syntax"
i have a form of Employee Info, where with navigation button i move to the next employee.
i have put a Command button to Preview Report on Single Employee Info that i am reading currently.
So i want to Filter the Report with Single Employee Name and records
so im coding on click even of the command button (see below) and its giving me null report.
im using where condition to filter the report and assign to it a variable employee name from the form. Reminding that source codes of both Form and Report is the same
X = Form_frm_MainCurrent.EmployeeName.Value
stDocName = "rpt_All"
DoCmd.OpenReport stDocName, acPreview, , "EmployeeName = X"
anyone got any clue to help me out?
Thanks for your time
i'm making form from query. but all i need to do is calculate days worked (TimeService) either worker still working or resigned.
i want to know is it possible to make it condition like it to query? if not can u tell me the other way?
Hi everyone, i have a form for bookings. People book by entering (among other stuff), the date and the booking slot.
Basically, what i need to do is have it so that only certain booking slots can be chosen on differnt days... for example, the booking slot of "Eve Extension" can only be used when the date refers to a Friday or Saturday, any other days it would not be allowed.
Is there any fool proof way of doing this, keeping in mind i dont exactly know VBA at all....
cheers, dragon
I got this code to run correctly, which pulls records where they are not junk(can be seen at end of where condition), the only issue is that the "AND" does not turn blue. Is this a problem?
Code:
If Not IsNull(Me.txt_last_rec_id) Then
'DoCmd.SetWarnings = False
SQLText = "INSERT INTO tbl_batt_id " & _
"(record_num, bat_id, manufacture, date_code, barcode, status) " & _
"SELECT " & Me.record_num & ", bat_id , manufacture, Date_Code, barcode, Status " & _
"FROM tbl_batt_id " & _
"WHERE record_num = " & Me.txt_last_rec_id & " And [Status] <> 'Junk'"
[code]....
I'm trying to put in a Where Condition for an ApplyFilter action, but it only allows up to 255 characters, I need more. I have very little experience using code!I want this to be the Where Condition:
Code:
[Program Name] Like "*" & [Forms]![Program List]![Text34] & "*" Or [Organization] Like "*" & [Forms]![Program List]![Text34] & "*" Or [Program Type] Like "*" & [Forms]![Program List]![Text34] & "*" Or [Main Office City] Like "*" & [Forms]![Program List]![Text34] & "*" Or [Main Office Province] Like "*" & [Forms]![Program List]![Text34] & "*"
How do I make this into the Where Condition? An full example would be nice.
how do i make 2nd combobox list based on 1st combobox list, mean if i choose A at 1st combobox, only A1-A5 option appear, and when i select B at 1st combobox, only B1-B8 appear to select at 2nd combobox..
TQ
hi ppl,
Could someone please help me with a query that I'm trying to create.
I need to create a query where once the DATE is overdue/expired how can i turn it into a red colour or something just to separate it from others.
e.g:
Length = 3
DateCleared = 31/01/2001
DateRenewal = 30/01/2004
--- obviously this date has expired and now i need to make it visible that it has expired. So I was thinking either turn it red.
thankyou.. :( :confused:
Ps. In order to create DateRenewal I used a formula by using the Length to calculate three years from the DateCleared.
I need to count the number of cells that match a certain number like >0 and <60. Thanks!,
Mike :confused:
Hi there, I currently have the below tables:
tbl_stores
ID NAME
123 Store A
456 Store B
tbl_returns
ID NAME RETURN
123 Store A 9999
123 Store A 8888
789 Store C 9999
I want to find the stores that exist in the stores table, but not in the returns table WHERE the RETURN is equal to 9999. This would hopefully return 456 Store B
My problem is when I put a condition in my query it returns nothing as the condition is based on the left joined table.
SQL
SELECT tbl_stores.store, tbl_stores.name
FROM tbl_stores LEFT JOIN tbl_returns ON tbl_stores.store = tbl_returns.store
WHERE tbl_returns.store Is Null AND tbl_returns.survey_id=2
GROUP BY tbl_stores.store, tbl_stores.name, tbl_returns.store
Any ideas? Thanks for your help with this one!
Cheers
Tony
Im trying to build a query that checks four fields for blanks. If I try to run the query with the criteria Is Null for all four fields, I only get 27 records. However, if I run the query with the condition only on one field I get the right number of records around 1000. I have been having to run four independant queries and then merge them in Excel. What am I doing wrong?
View 7 Replies View RelatedI have written some code that adds the address to the box dependant on base, but i get the error message
Compile error:
end if without block if.
Private Sub Report_Open(Cancel As Integer)
If Me.Base = "STN" Then Me.Address = "address here"
End If
End Sub
Can anyone explain this for me
I have searched, but I cant find a thread for this.
I have a calendar that I input when people are going to be off. If there is nothing for that date then the record is blank. I have a form that opens when the db opens attached to the date on the switchboard. If no one is off then the form opens blank because there is no date in the table to match. What I want to do is have a something built into the code that says if there is no match (null??) then dont open the form.
DoCmd.OpenForm "PopUpCalendar", acNormal, , "[Calendar]![Date] = Forms![Switchboard]![MainDate]"
Hi, im very new to visual basic programming...what i am trying to do is implement an If Statment on AfterUpdate for my form:
If record_is_new Then
Dim db As DAO.Database
Dim RS As DAO.Recordset
Set db = CurrentDb()
Set RS = db.OpenRecordset("LTOT_second_oxygen", dbOpenDynaset)
RS.AddNew
RS![HospitalNumber] = Me![general_info.HospitalNumber]
RS.Update
RS.Close
db.Close
Set RS = Nothing
Set db = Nothing
.........more of this kind of code
Else nothing
End If
any ideas on the vb code that would do this?