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.
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

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

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

770 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