Is it possible to update the datasheet view in a split form?

Posted on 2010-08-30
Last Modified: 2013-11-28
is there a way to update the datasheet view in a split form by adding a look up query in the form view? Every time I add a field, it adds a column to the datasheet view (which i understand is automatic) but I would like to search for a record and have all the information show up below.
Question by:jtovar3
LVL 10

Expert Comment

ID: 33560854
Please clarify.  "Every time I add a field" Are you adding the field to a table, a query or the form?

Are you dispaying all of your records on form load/open, and then filtering them down by some sort of input?

Also, what version of Access are you using?

If possible, inculde your db with some sample data.  That way I can see what you are looking at, and possibly just make a quick fix for you.

LVL 14

Expert Comment

by:Bill Ross
ID: 33561157

In form view of the form you want to change just modify the datasheet column widths, fonts etc. and click Save.


Author Comment

ID: 33561369
Sorry, here is my clarification.

I want to be able to add records and update records from the Add Record Split form. I was just wondering if there was a way to query and display the information in the datasheet view from the top form view.

For example... say i was in the Split sheet... could i include an interface search button at the top so taht it would only display those records in the datasheet?

I have included a sample database with sample data in hopes that it clarifies what I am trying to do.

i hope that makes sense. i can try and clarify further if necessary.

- In terms of the "adding fields" ... i had meant that when I add an input box to the form, it automatically adds a column, which I do not need.
LVL 74

Accepted Solution

Jeffrey Coachman earned 500 total points
ID: 33562390
<i include an interface search button at the top so that it would only display those records in the datasheet?>
You are just explaining what you want, not how this "Search" will function specifically...

In any event...
First, a split form displays the same number of records in both views.
The only difference is that the Top view(Typically in Single form view) will only display 1 record at a time.
So, a Split Form is designed to show the same records in both views, so filtering one world result in filtering the other.

So if by "Search Button" you mean that you will be providing a "Field Search Criteria" in an unbound textbox, then by clicking the button, this would filter both views to display records matching the criteria, ...then yes,... this is possible.

For example you want to filter the form for all records containing the exact string: "Tester" in the "Interface Description field.

Try this:



LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33562405
Just FYI:

Sample database notes:
Back up your database(s).
Combine the front and back ends into one database file.
Remove any startup options, unless they are relevant to the issue.
Delete any objects that do not relate directly to the issue.
Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
Compile the code. (From the database window, click: Debug-->Compile)
Run the compact/Repair utility.
Remove any Passwords and/or security.
Post explicit steps to replicate the issue.
Test the database before posting.

In other words, a database that we can easily open and "immediately" see the issue



Author Comment

ID: 33577642
@boagg that is perfect! Thank you so much for that and the advice on how to upload sample DB's

if you have time, could you please just explain a little bit about what you did so I can edit it to search a different field like interface number? but otherwise, that is perfect!
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.


Author Closing Comment

ID: 33577649
EXCELLENT! I just wish I knew what exactly you did so i can edit it and/or add more search fields

Author Comment

ID: 33577784
Another question... if I wanted to copy and paste it into my actual database... how would I go about doing that?

Thanks for all the help!
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33578693
Bear in mind that you have asked a question that requires that you be proficient in Form design and VBA Coding.

So when you ask:
" could you please just explain a little bit about what you did so I can edit it to search a different field like interface number?"
I don't know how to explain it if you don't know Form design or VBA Coding.
Basically I added the text boxes and set the Form's Filter Property based on the values in the textboxes.
(is this the: "explain a little bit about what you did... " that you wanted?)

As a show of good faith I have adjusted the sample to search two fields.

The code is commented, so you will have to read through the code and adapt it to your specific database.
This code can be viewed by pressing Alt+F11.

Also you should pick up a good book on From design and VBA Coding for MS Access



Author Comment

ID: 33579818
Thank you so much @boagg, like i said this code is exactly what I am trying to do.

I am familiar with VBA but my issue is how to use it in Access. Honestly, this database was supposed to be my introduction into creating a database, but my bosses have been asking for functionality that I just don't know how to do yet. I guess this is a kind of Trial By Fire.  X_x

I still have issues taking your code and getting it to work in my database. When given code like this, should I be importing the whole form into my database, or can I just put in the code into "Code Builder" option using Build Event.

I copied over the text boxes, and the buttons, then went to the code builder and copied in your fabulous code, but it's still not working. What else do I need to change/adjust?

You have been so helpful. Thank you!
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33580878
Well for starters you must change all the object names in the code to match the object names in your actual database.

So make sure the Button Names and the textbox names are correct for your database.

You should probably change the variable names as well so it is more descriptive of your database.

So if you are searching for last name instead of SAP, then the variable would be renamed to: strLastName.

Also, remember that this is the sample you submitted.
So the presumption was that this would work in your database directly.

If you are not good at adapting code, then perhaps you should post a sample that is more representative of your actual database.


Author Comment

ID: 33589370
Okay I finally got it working. I just needed to sit down and focus on it instead of being pulled away every 15 minutes. Thank you so much again!

Is there a way i can award extra points? haha
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33589931
<Is there a way i can award extra points? haha>

As long as I have helped you in some way, this will be enough...



Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now