Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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

Posted on 2010-08-30
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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.
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

LVL 74

Accepted Solution

Jeffrey Coachman earned 2000 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, ...post 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!

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

604 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