I have a form that lets you add records to my table, it works well and I can see the records in my table but then once I use my search form, which also is working well, the new records won't come up when I do a search, it just keeps searching through the records that were there when I created the search form.
View RepliesBasically I have one table with all relevant information needed, etc, and I have the original spreadsheet data inputted to the database. The original data appears fine when a search is done, eg, the user searches for something using a form that then uses a query and ultimately brings up the information that matches. In the queries, I use the same basic criteria
"LIKE "*" & [Forms]![FormName]![FieldName] & "*" "
My question is, now that I have a form to add a new record to the table so a user can add to it, what would be the best way to get the added record to appear during the search, like the original records? I have used " IS Not Null" at the end of the criteria within the query, but it tends to bring up all the new records no matter what the user searches.
I created a database. exported some Excel data into Access tables, created a number of lookups, default values etc. and created a pretty simple query.
My problem is that after importing and tweaking the data, when I add new records, my queries do not pick them up!
I have tried:
1. saving, closing, opening and re-running the queries.
2. putting an Nz expression for each field in the query as I read that null values may cause a problem.
3. wrote the query again, field by field to see if all records were received.
4. Exported the table back to Excel and imported to a new Access table in my database
None of this works.
I have a combo box that pulls account name data from tblAcctInfo. the combo box has an OnChange event which updates a textbox, txtAcctAddr.
when i have a new account that i would like to be listed in the combo box, i use a form, frmAcctAdd, to add a record to tblAcctInfo.
what i would like to do, is:
1) when i type a new value in the combo box that isn't in the list, have that string value pre-populate in the frmAcctAdd.
2) when i have added the new account info into frmAcctAdd and then saved the record, i would like the new value to pre-populate in the combo box, with the txtAcctAddr textbox also updated via the OnChange event (or maybe a different event is more appropriate?).
I have created a long version of this which requires a lot more user interaction (1-user typing in a new value into the combo box, 2-user RE-typing the SAME value into a data entry form, frmAcctAdd, 3-user saving the new record, 4-user re-clicking the combo box and selecting the newly added value) but i am trying to streamline the data entry with auto-populated fields.
I have an Access 2010-form which inserts a record in a MS SQL 2008-database by using an ADODB-connection. I need to retrieve the primary key of the newly added record.
With code at the bottom I create a SQL-string which is stored in variable strSQL. If I execute the SQL-string directly in a MS SQL 2008 Query Window, the record is inserted and the MS SQL 2008-result pane shows a column "ID" with the primary key of the newly added record.
If I execute same SQL-string in MS Access 2010 the record is inserted. However, the code fails on Debug.Print rs![ID] with error "Item cannot be found in the collection corresponding to the requested name or ordinal". Same error appears if I use Debug.Print rs.Fields(0). I've enable the reference "Microsoft ActiveX Data Objects 2.8 Library". It looks as the recordset rs is closed as soon the command "Set rs = cn.Execute(strSQL)" finished.
Why I can't retrieve the Primary Key with VBA-code below?
Code:
Private Sub cmdSave_Click()
dim cn As ADODB.Connection
dim rs As ADODB.Recordset
set cn=New ADODB.Connection
[Code] .....
I have a piece of code that is creating new records when the form loads using recordsets.
I need a subform to show only the newly created records
I have an append query that appends records to a table, and I have a form based on that table.
Users will click a button that will run the append query and then open a form for users to fill in remaining empty cells. How can I filter the form to show only the newly appended records?
I have a subform that has 3 check boxes. If the record was an existing record in the SQL table and I want to change the value I can change it no problem. If I try to check the box for a new record added to the database then it doesn't allow me to save the change. I know that this is a problem with bit values in SQL when deleting so I made sure all 3 fields have a default value, but it still doesn't let me make changes to new records.
View 1 Replies View RelatedI have a form that has a few combo boxes that I use as drop down lists that the user can choose from. After the form is completed, the user clicks "Submit" and the query/table is updated. However, 2 records are added instead of 1. The first record leaves the first field blank and every other field completed. The second record has every field filled out with the same info as the first. In other words, the first of the 2 records is unnecessary. How do I eliminate this from happening?
Data Entry set to "Yes"
Submit button:
Code:
Private Sub cmdSubmit_Click()
DoCmd.RunSQL "Insert Into qryEarlyPoints(empName,dateOfOccurrence,leaveEarly,early6Mins) VALUES('" & Me.txtEmpNameInf & "','" & Me.txtDateInf & "','" & Me.cmbEarlyPoints & "','" & Me.cmbArriveEarly & "')"
Application.SetOption "Confirm Action Queries", 0
Application.SetOption "Confirm Document Deletions", 0
Application.SetOption "Confirm Record Changes", 0
DoCmd.Close
cmdSubmit_Click_Exit:
Exit Sub
End Sub
I have a form with a dataset based on a table. (there are a number of buttons on the form with code so it's important I don't delete the form and start again with a new one)
The data displays correctly but the order is wrong. It displays the last record added as the first record when viewed in form view.
I want to reverse this order, how do i go about it? It seems very simple but I've yet to find a solution.
I also don't want to base it on a query because of the code mentioned earlier.
I can add record successfully... but can't view it accordingly when I press navigate buttons in the form...
Database is attached ....
About a year ago I created a search form based on Allen Brownes Search model. Working fine, but the user wanted added functionality, see here, after a few tries the users didn't wanted the option anymore because they were in a hurry to deploy. But now their management is asking again for a wider scope in that state search.
I made an example model. It works fine, but only if I always have some value in the [State C] option, if left blank it won't work, a Run-time error 3075, will pop up.
I have tried many combinations of parenthesis, but, haven't got any luck.
Is it possible on the Find Record button added to a form to adjust the search function so it defaults to a specific box on the form? I have a form for tracking employee's and on my Find Record button I would like it to default to the Last name instead of the Record number.
View 3 Replies View RelatedI launch a 'CreateNewRecords' form from a 'MainForm' form. When the 'CreateNewRecords' form closes, the new record is visible on 'Main Form'.The underlying query is unsorted, so this code in the AfterUpdate event of 'CreateNewRecords' puts the cursor on the last record displayed on 'MainForm', which is the newly created record:
Forms!frmMainForm.Requery
With Forms!frmMainForm.RecordsetClone
.MoveLast
Forms!frmMainForm.Bookmark = .Bookmark
End With
However, I intend to sort the underlying query, which means that a newly created record may appear in the middle of the records displayed on 'MainForm'.
What I want to have happen is that after a new record is created by 'CreateNewRecords', the underlying query is requeried so that all current records are displayed on 'MainForm' but also that the cursor rests on the newly created record (rather than default to the first record).
I have form setup with two cascading combo boxes (facility and date) to select a record (other navigation, including mousewheel, is disabled). There is a button to open another form which allows addition/deletion of records by facility and date. When this form is closed, the combo boxes update accordingly on the initial form. However, if I select a new record whose facility/date I have just entered, it seemingly shows the wrong values for several of the other fields. If I check the corresponding table, the values are actually at default, though, and when I reopen the form they have reverted to default. Any ideas on how to solve this? I'm tempted to simply have the first form close and reopen when the second is closed, but couldn't figure out the syntax to close a form with a button on a different form. Let me know if there's any more information I need to provide. Thanks for any suggestions.
View 9 Replies View RelatedHello,
I have created a form to enable data to be easily added to the database table, however does anybody know why the query that looks up the records from the table does not return the new entered records, only what was originally in the table?
Thanks
I have 2 sub-forms attached to a main form. Each of these forms are based on different tables. Whenever I input the record, it goes into respective tables. However when I try to delete the record, it only deletes the record from the main form. I tried cascade delete. However, since referential integrity has to be established, I cannot add the record to my subform because there has to be a corresponding record in the parent table for me to add the record in the subform.
View 2 Replies View RelatedI need a calendar that can go forever that shows weeks and not months and each record i add (or is added by admin form) will show up. I have a calendar attached here to show what i mean but its monthly not weekly.
Basically it needs 7 spots for employee vacation leave, 2 spots for supervisor leave and 2 spots for other leave. My current calendar will only fit the 7 employees and no1 else thus the reason i want to go weekly and not monthly, so it has more room.
I have several forms in a navigation form. On all the forms, the little search bar at the bottom shows # of 103 records. On my navigation form it shows 1 of 1 and won't allow me to search.It would seem that the search bar is only looking at the current form and not at the records.
View 1 Replies View RelatedI have used John Big Bootys Dynamic Search Form, however to search on the fields I want I have created a query which has mutiple tables and when I get a duplicate value in the search box, i.e. in Client, there are 2 client "A"s and when I click on one it automatically chooses both.From here I want to create a double click event to open that records in a form, but I can't if it defaults to both records.
View 1 Replies View RelatedI am building a search form in my DB. I have got this working by using a text search box on the main form which then links to a subform that shows the results. This works great, however, when the form is first opened, it shows all the records that be searched. Is there any way that I can stop this? I just want the subform to be blank until a search is ran.
View 3 Replies View RelatedI have a search button / text field on a form with the following code:
Private Sub SearchButton1_Click()
Code:
Dim rsTemp As Recordset
If IsNull(Me.SearchField1) = False Then
Set rsTemp = Me.RecordsetClone
rsTemp.FindFirst "[CompassRef] = '" & Me.SearchField1 & "'"
If Not rsTemp.NoMatch Then
Me.Bookmark = rsTemp.Bookmark
[Code] .....
Unfortunately it only returns the 'No record found' MsgBox, even if SearchField1 is populated with relevant content.
Hi There,
I have got this form, consisting of 1 form (that needs to add a new record to a table everytime) and 1 subform, that needs to show the last three records of the table.
My problem is that I cannot get it right, now both forms (form and subform) are showing the details on just one record. If I open my subform it shows me all the records of a table, but in as soon as I view the form as a subform I shows me only one record.
Any help is welcome, I attached my db, just in case....
I have an existing table that has calculations. When I added a new field with an default value of zero, it did not populate the existing records. Now my tables are not calculating. How can I update the existing records with the new default of zero.
View 2 Replies View RelatedI have a database created in Access 2003 about six years ago. I’m the sole user of the Application, which I use to keep track of my personal assignments and to quickly access other resources. A search form was added and tweaked over time until it contained the features I desired. The search form uses a subform to display a list of records, with the ability to click a record‘s "Edit" field to open and update that record.
In about March 2014 we upgraded from Office 2003 using Windows XP to Office 2010 using Windows 7. All users on other Applications that I’d designed and support experienced missing Reference issues, but those were resolved rather quickly. I'd been off work intermittently for about six weeks between April and July and gradually started noticing other issues with my personal assignments tracker that I did not immediately associate with the upgrade. Early on, I noticed that I could no longer delete or rename database objects on several of the databases that I'd designed and support. Eventually, I also noticed that I was unable to create new records and received SaveRecord error messages associated with the subform. I'd encountered SaveRecord issues before and went through previous steps to resolve, but to no avail. Eventually, I came to the conclusion, right or wrong, that all of my recent issues were associated with the upgrade.
I found through online research that my loss of ability to delete or rename Objects was possibly due to the loss of Object Owership and Permissions, which was associated with my Security Workgroup. I confirmed that Ownerships and Permissions had been lost and was unwilling to recreate the database, just yet. No user of other Applications that I support reported any problem creating new records.
I know now that new Access doesn’t have the Security menu item that allows Workgroup creation and joining. After months of online research and trial and error, I was directed to Microsoft Knowledgebase Article 918583, which provided VBA code that allowed me to Run the Workgroup Administrator to rejoin my previously established Workgroup.
After rejoining my Workgroup, everything seemed to return to normal. I could delete and rename records and was able to create new records. After a few days, I noticed that new records were not being displayed in the search form right away. I confirmed that the records were being added to the main table, but still not available in the search form, queries, or reports until after an additional record had been added. In other words, for example, in order to view record 1, a record 2 would have to be added. In order to view record 2, a record 3 would have to be added, and so on. This makes it very tedious to enter new assignments.
I believe the present issue is greater than my search form, because the newest record is not only not available in the search form, but not available in any other form, query, or report. I have no clue where to start.
I have a form that when initially displayed shows all client records in my database with the ability to add new ones through a "Add New" command button. I have another command button called "Search" that displays a second form containing a dozen or so fields to search and filter down on.
When the Search button is pressed, I display search form and enter data in some of the fields for searching. I then click ok and the main form should now display a subset of the records based on the data I entered on the search form.
My dilemma is that the WhereCondition of the docmd.OpenForm doesn't work. The doccmd works to open the form when I don't include the WhereCondition, but the moment I put the WhereCondition in, I get an unknown runtime error (3000).
Here's the code. I've stripped out most of the search fields to target just the one until I can get it working, then I'll add the others back in.
Private Sub cmdSearch_Click()
Dim varWhere As Variant
' Initialize the search string to Null
varWhere = Null
If Not IsNothing(Me.srchLastName) Then
varWhere = "([p_last_nam] LIKE '" & Me.srchLastName & "*')"
[Code] ....