When I open my access 2007 database from the switchboard, the form that it opens up to is a parent form with a subform embedded in it. The subform is linked by the 'org name' and by 'year'.
I am finding that when I open the parent form,there is always an additional empty record in the subform, alongside the record which has data in it.
I am not sure why it is doing this, especially as when you enter another record, this empty form vanishes. Its almost acting as the default form?
I have also noticed that when I go to another form which is displaying specific data linked to the 'org name' and 'year' it also has an extra record
However, the weird thing is it does not show up in the table. And again, once you go to a new organisation and input some data, and then go back to the other organisation,the additional record has gone?
Is there a simple property setting whereas each time you open the form, it always opens up to where you essentially left off, no empty record?
I always have a problem with input forms in Access in that I usually end up with an empty excess record which is being created because I do not know how to program this correctly.
I have a Purchase Order Receipt form (frmPurchase). When I receive a certain quantity of an article associated with a lot number (or a serial number) I have another input form opening up (frmLotorSerial), passing to it through Openargs, the article and the quantity received.
Say we receive 10 units of which we want to register the serial number, I want the user to be able to enter a maximum of 10 units (could be 10 records if each record = 1 serial number, but it could be less than 10 if we have several units per lot number), and not one more, into this frmLotorSerial.
The record looks like this: -key -artno (article) -recqty (quantity received) -date -warehouse -lot number
I used the lost_focus event on lot number to check whether the total received quantity in this form matches the total passed to it from the Purchase form, and if it does to stop the input. Since I could not close the window in the code of this event (error 2585) I moved the close command to the current event of the form.
Doing this closes the form all right, but it always creates one record extra, which of course does not carry any information, apart from the date which has a default value of now.
How should I program/what events should I use to: -test that the user should not go any further (total received in frmLotorSerial = total received in frmPurchase) -close the window and, above all, not create this extra record.
I have a table t1 which contain many records, the field look like:
id f1 f2 f3 ---fn
Id is the primary key I have some records which have different id but exact the same other fields. like id f1 f2 f3 ---fn 87 1 4 6 ---9 12 1 4 6 ---9 18 1 4 6 ---9 116 1 4 6 ---9 1287 1 4 6 ---9 98787 1 4 6 ---9
for those records, I only want to keep one record (any one) and remove all others. How can I do that?
I currently have a database which has a fairly simple form linked directly to a table, which records staff attendance information.
It generally works really well, however there is one thing I would like it to do, which is be able to hide certain records from view (rather than delete them completely).
I have a checkbox on the form (bound to a corresponding field on the table) called chkHide (the field in the table is called hide), which is not visible in form view; I have a button that is visible and when pressed by a user it will change the value of the check box to True.
So far so good. However, I would then like to have the form refresh and when it does so no longer show the record to the user (so it looks like it has been deleted, but in fact that record remains in the table).
I have tried applying a filter on load, but I don't know what expression to put in the Filter on the Property Sheet - - I have tried [Hide] = Is Null, but that isn't right. I have also created a Query where I have set the criteria for the field 'Hide' to Is Null and then put the name of the query in the Filter box - that doesn't work either, as it will let me put in new records but not let me see any of those put in before the form was refreshed.
Good Evening Everybody, I am currently helping some people out at work with their database. My knowledge of Access is very limited, and whilst my it is improving I have nonetheless come up against a ‘hurdle’ which I am struggling to jump. The database in question is not complex, quite the opposite in fact. Basically 1 table ‘Main Data’ , 1 data entry form and a few reports. The table includes 8 fields in the following formats:
ID: Auto Number Area: Text Equipment:Text EquipSrlNo:Text EquipLocalNo:Text MOPNo:Number DateDone:Date/Time MOPPeriodicity:Text
I was recently asked to produce a report for them that would search between two dates using a parameter query and to then return the result. The problem is that when I generate it using the following SQL it returns records in the period that I asked for, but then collects ‘additional records’ which fall outside the date-span that I originally requested, i.e. search Between 03/04/08 And 30/04/08 produces data that relates to data in May, June, July 08 etc. I thought that what I was doing was correct, and it has worked for me in the past- but on this occasion I am absolutely confused???
SQL SELECT [Main Data].Area, [Main Data].Equipment, [Main Data].EquipLocalNo, [Main Data].MOPNo, [Main Data].DateDone, [Main Data].EquipSrlNo, IIf([MOPPeriodicity]="1",DateAdd("m",1,[DateDone]),IIf([MOPPeriodicity]="3",DateAdd("m",3,[DateDone]),IIf([MOPPeriodicity]="6",DateAdd("m",6,[DateDone]),IIf([MOPPeriodicity]="WEEKLY",DateAdd("ww",1,[DateDone]))))) AS TestDueNext, [Main Data].MOPPeriodicity FROM [Main Data] WHERE ((([Main Data].Area)=[Enter Area Type]) AND ((IIf([MOPPeriodicity]="1",DateAdd("m",1,[DateDone]),IIf([MOPPeriodicity]="3",DateAdd("m",3,[DateDone]),IIf([MOPPeriodicity]="6",DateAdd("m",6,[DateDone]),IIf([MOPPeriodicity]="WEEKLY",DateAdd("ww",1,[DateDone])))))) Between [Enter Date Start] And [Enter DateStop]));
Unfortunately, I do not have a copy of the database in question and so I am unable to post it for the purpose of explanation.
Can some kind person point me in the right direction as I am slowly losing the ‘will to live’ Ha? Ha? You have always been so helpful in the past and hence the reason I have called upon my ‘forum friends’ to help me out?
I have a main form (ZooMobile Booking Form-New Client) with a subform (ZooMobile Event Booking-New Client) within it. In the main form I have a button to open up a report displaying the main form's current record.
The report that is opened has a subreport that needs to display the current record from the subform.
So far I've managed to get the report to open to the main form's record using the following On_click command:
I can't manage to get the subreport to open to the current record in the subform, however. I've tried to integrate it into the above event and to use On_load events in the report and subreport but everything either comes up as a bug or has no effect on the subreport's record.
The user select the class from [Combo28] for the unit already in [Textbox named Unit] from Form 1 and wants to list all the students for that particular class and unit in form 2.
I have tried god knows how many different codes. I've been playing with:
I need to add HolDte and make it also use HolidayDate as it's criteria.
PHP Code:                strSQL = "INSERT INTO tblHour (WorkDate,Hours,HolDay,EmployeeID) " _ & " VALUES  (#" & Me.HolidayDate & "#," & Me.txtHrs & ",True," & Me.EmployeeID & ")"Â
Customer Table > Customer ID (pk) 1 Issues Table > Customer ID (FK) Many Complaints Table > Customer ID (FK) Many
Each table has Customer ID as you can see, I have linked the Customer ID From Issues & Complaints table to the Customer ID field to the Customer Table using a 1 to Many relationship.
Am creating a form on the customers table so an admin can add a customer issue against the customer this is working fine, The trouble is when I link the Complaints table as well. its asking for subdatasheets.
I have a continuous subform with allow additions set to false. To make a new record I have used some update vba to create the record direct in the underlying query, then requery the form and the partly created record appears. The user then adds a quantity and some text. The subform still appears without the new record line.....However if I click the button again to create a second new record I end up getting an extra 2 lines.
One is a duplicate of my previous one and a new blank record. These do not actually appear in the underlying table and the subform looks ok. However this extra record confuses the end user and I want to avoid it. Refresh or shift f9 does not eleviate the problem. Sometimes I even get two "current record" pointers.
I have a Form opening from Access Options. I would like to close this Form using the Timer. The following is the code I have used but it is not working.
Private Sub Cover_Page_Form_Load() OpenTimer = Timer End Sub Private Sub Cover_Page_Form_Timer() If (Timer - OpenTime) = 5 Then DoCmd.Close acForm, "Cover_Page_Form", acSaveYes End Sub
Next question. If I can get this to work can I then use a DoCmd to open new Form within the code above or do I need a new process.
My form was working fine but when I added an extra tab it started showing as completely blank in design view. When I deleted the tab I had added I still was left with the same problem!
I've attached an image of the form properties. I want to use the form to enter data so it needs to show even if there are no records.
I had read that it could be if there is no data but typing data in the tables hasn't worked either.
Hi All,I'd like to see a messagebox when I open a form (based on a query) that contains 0 (zero) records. I have a txtbox on the form that counts the numbers of records so I thought it'd be easiest to use that number and have the following VBA in the "on load" event:Private Sub Form_Load()Me.txtCount.SetFocusIf Me.txtCount = 0 Then MsgBox "this folder does not contian any records", vbExclamation, "No records found" End IfEnd SubI get error message 2474 (I've entered an expression with no value).What would be a better solution?
I have the following code in the click event of my button, Code:Dim strCriteria As StringstrCriteria = "[1stApproval]='-1'"DoCmd.OpenForm "FrmLookupRecords", acNormal, , strCriteria
However when I click the button, there was an error that says, Quote: The OpenForm action was cancelled. You used a method of the DoCmd object to carry out an action in Visual Basic, but then clicked Cancel in a dialog box. For example, you used the Close method to close a changed form, then clicked Cancel in the dialog box that asks if you want to save the changes you made to the form What does this error mean? I want to open a form that only shows approved records and the column "1stApproval" is a Yes/No field. Thanks for any help!
I have a form in datasheet view that has a user directory with a list of each person. On each row you can click to open another form that contains the users details using the below VBA:
But once you open to that specific record, no other records are available to navigate to.
I want to be able to stay on that detail form that was opened and go to another record.
How do I continue to pull up that specific user in my detail form, but also load the others so I can navigate to them from the detail form without having to go back out to the datasheet form?
I have a listbox that another subform on the same main pulls results from the column(0) id from (using text box that pulls the list record selected)
When you click once it (listbox) shows the records in the second form for the corresponding. When you double click it opens the record of the selected (listbox)
This works great in Runtime 2010 for all my 32bit staff and vista
however in 64bit win 8 with any runtime including 2013 64bit the database listbox works for a while, then slows and then stops opening the record on double click and when I say slows it slows to a crawl speed like it is looking up the query in the subform instead of just opening the record with the double click.
I was thinking mouse double click settings but they seem fine and as I said before it works fine for a while.
I'm trying to get a combo box & button to work together. On the main form of the attached database, I just want the button to go to the associated record based off my selection (or entry) in the combo box.
I've tried the following:
- Looked through the Northwind sample database for a close example. Tried my best to replicate the functions but it didn't work - Looked through other examples posted and tried to manipulate the code to no avail - Tried using [Forms]![Main Form]![cmbLastName] in both the filter & criteria section of the macro
the forms are not opening in modal/dialog mode when they should be.The forms' pop-up and modal properties are set to "yes"..The code specifies to open Dialog so, for example:
Yet, the form opens behind the other form (which, incidentally, is also set to be pop up and modal in the form properties--is that the problem? If so, why wasn't this an issue until now?).
I have a main form with multiple subforms in tab view, right now the forms only open in edit mode, I would like all forms to open to new record mode. I know I can set the main form with the switchboard manager but what about the subforms?
For some strange reason, a replica db is being created along side the original. I don't believe it's a back up since the name back_up usually appears in the file name. I've seen it happen once when the proper mdb file was opened and then noticed the new db was created (always called db1.mdb). It's a permanent file mdb file and not the record locking icon. I remember over the summer someone posted a similar problem but I haven't been able to locate that thread. Any suggestions?
Although I have been using access for a year or two (self taught) I have only recently noticed that the form toolbox (the one with combo boxes and buttons etc on it) appears to have a library of procedure calls for calendars and all sorts of things sitting there waiting to be used. Is there a list somewhere of what all this lot does? I have been playing with a couple of date pickers but I don't particularly want to go through all of the rest to see what they do or if they would be useful.
Hi Does anyone know how to delete the extra blank fields you get at the end of a query. I am converting the query to a handheld database with a field limit and the extra fields push my field count over the limit. i have tried just 'deleting' them but they reapear when I reopen the query. they definitely count as fields because if I delete some real fields i can do the conversion. i dont want to alter the structure of the original database as it has been in use some time and works well. any ideas advice Thanks supateach
Hi again I am still having problems with too many fields in my query so it will not convert to a handheld database. I have tried making a test query (with the wizard)from the same table and sure enough there are extra fields at the end, which do not come from the table, which reappear after they have been deleted, when you reopen the query. I have attatched the query to illustrate that I have deleted the fields and then they reappear. It does appear to be impossible to get rid of them, in which case I shall have to decrease the number of 'real' fields to get below the max the handheld can convert. Any advice gratefully received Thanks supateach
I have the following expression, which needs to be wrapped by some extra logic:IIf([NetTotal]<>0,IIf([NetTotal]-[InvoicesRaised]>0,30,[InvoicesRaised]/[NetTotal]*30),0)What I need to do is, if the above expression comes to less than 0, then the control box needs to display 0, otherwise it should display the value of the expression.Now, I know I can do this by IIf(expression<0, 0, expression). However, this adds a lot of code in there and the expression is put in twice. Is there an alternative way of doing this ?
