Using Combo Box with Datasheets (1 Viewer)

tmyers

Member
Local time
Today, 03:55
Joined
Sep 8, 2020
Messages
370
Apologize for the poor title. I was not able to nicely articulate it in a short version.

I have a form that I would like to have a text box that will pass text/numbers entered into a combo box. Below those will be a datasheet. I want to be able to enter say E-104 in the text box, hit enter, then that value appear will as a selectable option in the combo-box. That is easy enough for me to figure out. The next part is what I have not been able to resolve.

Once you load the combo box with the value, I want the datasheet below it to be "tied" to that particular entry. So if you enter values while the combo box has E-104, those values are saved under that ID. If you change the box to E-105, the datasheet will show any values entered under that value etc etc. So what is displayed in the datasheet depends on the value selected in the combo box. Essentially I am trying to use the combo box as a holder of an ID that I want the datasheet based off of.

Is something like that doable? Are there better methods? I had made a post a few days ago about using dynamic tabs in a tab control, but I think something like this would be a cleaner solution.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:55
Joined
Oct 29, 2018
Messages
13,239
Hi. It doesn't sound very complicated, but we can't be sure without seeing it. It sounds like you simply want to "filter" the datasheet area. Is that correct?
 

tmyers

Member
Local time
Today, 03:55
Joined
Sep 8, 2020
Messages
370
Ah filter. That is the word I was looking for. Yes. Use the combo box to filter the data sheet to that values ID, otherwise remain blank if no value is selected in combo box.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:55
Joined
Oct 29, 2018
Messages
13,239
Ah filter. That is the word I was looking for. Yes. Use the combo box to filter the data sheet to that values ID, otherwise remain blank if no value is selected in combo box.
Go ahead and give it a try and let us know how it goes or if you get stuck. Cheers!
 

tmyers

Member
Local time
Today, 03:55
Joined
Sep 8, 2020
Messages
370
I have been stewing over this for a bit, and have not quite figured it out. I have the data sheet on my form, it is filtered by one ID already (my quotes table ID number) so that way it only loads in info relevant to that quote. How would I filter it one more layer? Currently, it is filtered via a criteria in my query, so it only returns data for that QuoteID.

I attached my DB for reference. On the form "QuoteForm" I have a subtab under the "Take Off" tab named "Counts". What I want, is to have a combo box (which I havent added yet), to input the Sheet Name (and when that value is added to the combo box, it is added to the table). That way the users dont have to type out the sheet name every single time for every type in the list.

Does that make sense? If not, i can try to better explain it.
 

Attachments

  • Example.accdb
    1.7 MB · Views: 21

theDBguy

I’m here to help
Staff member
Local time
Today, 00:55
Joined
Oct 29, 2018
Messages
13,239
What I want, is to have a combo box (which I havent added yet), to input the Sheet Name (and when that value is added to the combo box, it is added to the table).
Which table did you want the input to be saved?
 

tmyers

Member
Local time
Today, 03:55
Joined
Sep 8, 2020
Messages
370
Knew I was missing details. The table I need to work with is "Sheets". The textbox will enter the value to the table, which will be the rowsource of the combobox (somehow filtered to only show sheets related to that quoteid).
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:55
Joined
Oct 29, 2018
Messages
13,239
Knew I was missing details. The table I need to work with is "Sheets". The textbox will enter the value to the table, which will be the rowsource of the combobox (somehow filtered to only show sheets related to that quoteid).
1600970127988.png
 

tmyers

Member
Local time
Today, 03:55
Joined
Sep 8, 2020
Messages
370
Above the datasheet. It can either then insert its value down the sheet field, or be a total control of it that makes the field in that datasheet irrelevant. I dont know why this one if being so hard for me to articulate.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:55
Joined
Oct 29, 2018
Messages
13,239
Above the datasheet. It can either then insert its value down the sheet field, or be a total control of it that makes the field in that datasheet irrelevant. I dont know why this one if being so hard for me to articulate.
Okay, I was trying to do this for you, but I ran into a problem because I am not familiar with your db.

So, if I put a dropdown where it shows E-104 and then enter E-105 instead, I understand you want E-105 to be saved to the Sheets table. However, I can't seem to do that right now because the Sheets table is asking what TypeName should I use for E-105.

So, the question for you is, how do you envision the user entering something in the combobox for a new Sheet and somehow ask them also for the TypeName?
 

tmyers

Member
Local time
Today, 03:55
Joined
Sep 8, 2020
Messages
370
My thought process was that it would pull the TypeName from the Query based on the data entered in the "Types" tab. That is why the "Type" field under Counts was already populated.

Once types are entered, they should not change for the entirety of the job.

My first idea was to have multiple tabs, one for each sheet (you might remember me asking about that), but the more I dug into it, that seemed like a poor way of doing it. There may be yet another/better way of doing it, but I have not yet thought of anything. I have to separate the data somehow, because counts for each type can be different for every sheet entry.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:55
Joined
Oct 29, 2018
Messages
13,239
My thought process was that it would pull the TypeName from the Query based on the data entered in the "Types" tab. That is why the "Type" field under Counts was already populated.

Once types are entered, they should not change for the entirety of the job.

My first idea was to have multiple tabs, one for each sheet (you might remember me asking about that), but the more I dug into it, that seemed like a poor way of doing it. There may be yet another/better way of doing it, but I have not yet thought of anything. I have to separate the data somehow, because counts for each type can be different for every sheet entry.
Hi. Sorry, I didn't wait for your response and continued working on it. Please see if the attached is close enough or if you can tweak it to do what you want. Otherwise, I'll read your post again and see if I can understand it. Cheers!
 

Attachments

  • Example(1).zip
    179.2 KB · Views: 18

tmyers

Member
Local time
Today, 03:55
Joined
Sep 8, 2020
Messages
370
Thank you for your help as always!
I will take a look and try to adapt it.
Thanks!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:55
Joined
Feb 19, 2002
Messages
30,132
If you are trying to populate all rows with a single value, then that value is being placed in the WRONG table. It belongs in the parent table or perhaps you need to create an intermediate table between the existing parent and child.

I attached a picture of a complex form from an application that manages construction drawings for a steel fabricator. The filter section for the drawing subform is highlighted in yellow. At the right edge is a button that turns the "filter" on or off. I've also attached the code for that button. There is a hidden field that holds the status of the filter so it can be toggled on and off. Access form filters are NOT used. The actual filtering is accomplished by criteria in a query bound to the subform.

SubFormFilter.JPG

Code:
Private Sub cmdFilter_Click()
    If Me.txtFilterOn = True Then
        Me.txtFilterPfx = Null
        Me.cboFilterDwg = Null
        Me.cboFilterSfx = Null
        Me.cboFilterType = Null
        Me.txtFilterOn = False
    Else
        Me.txtFilterOn = True
    End If
    Me.sfrmDrawings.Form.Requery
End Sub
The Query
Code:
SELECT tblDrawings.DrawingID, tblDrawings.JobID, tblDrawings.DrawingPfx, tblDrawings.DrawingNum, tblDrawings.DrawingSfx, tblDrawings.FullDwgName, tblDrawings.DrawingTypeID, lkpDrawingType.DrawingType, tblDrawings.Desc, tblDrawings.DwgSize, tblDrawings.InactiveInd, tblDrawings.ResubmitInd, tblDrawings.Comments, tblDrawings.DrawingID, ([DrawingPfx]+" ") & funcRJust([DrawingNum]) & (" "+[DrawingSfx]) AS DwgSort, tblDrawings.OnHoldDT, tblDrawings.OnHoldReason, tblDrawings.UpdateDT, tblDrawings.UpdateBy
FROM lkpDrawingType INNER JOIN tblDrawings ON lkpDrawingType.DrawingTypeID = tblDrawings.DrawingTypeID
WHERE (tblDrawings.JobID = [forms]![frmJob]![JobID]) AND
(tblDrawings.DrawingPfx = [forms]![frmJob]![txtFilterPfx] OR [forms]![frmJob]![txtFilterPfx] Is Null) AND
(tblDrawings.DrawingNum = [forms]![frmJob]![cboFilterDwg] OR [forms]![frmJob]![cboFilterDwg] Is Null) AND
(tblDrawings.DrawingSfx = [forms]![frmJob]![cboFilterSfx] OR  [forms]![frmJob]![cboFilterSfx] Is Null) AND
(tblDrawings.DrawingTypeID = [forms]![frmJob]![cboFilterType] OR [forms]![frmJob]![cboFilterType] Is Null)
ORDER BY tblDrawings.DrawingPfx, funcRJust(tblDrawings.DrawingNum), tblDrawings.DrawingSfx;
 

tmyers

Member
Local time
Today, 03:55
Joined
Sep 8, 2020
Messages
370
Thank you @Pat Hartman. That example database in the picture is ironically essentially the database/program I am trying to build. It would instead be for electrical construction instead of steel. I am using the same general structure however with drawings (I have them dubbed "Sheets" in my application) and such.

I had a feeling over the weekend while thinking about this that my table structure for this particular aspect is probably not correct. That would explain why I have been having issues resolving this, as my foundation is built incorrectly.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:55
Joined
Feb 19, 2002
Messages
30,132
It doesn't matter what type of construction you are managing. Drawings are drawings and there are tons of them in large projects. Prior to me building this app, the client was using books to log the drawings as they came and went for various approvals and went through various revisions. Their librarian was excellent but there's lots you can't do with this type of a paper system so the client has been using this app for almost 10 years and still love it. It imports KSS files or spreadsheets so they don't have to do manual entry for new projects/drawings. It is linked to their accounting system so it can get/sent information there. It's also linked to their library of drawings on the server so an engineer can actually dblclick on the drawing number and view the actual drawing. It automatically sends emails every week to the managers listing drawings that are late being returned. Every once in a while I add a new report or data field but it is pretty stable although we are talking now about converting the BE to SQL Server in case I actually want to retire from all support work. It will probably take me half a day to do that, mostly because it will take a lot of testing. But, I build all my apps with the intention of ultimately having SQL Server or other RDBMS BE's so it's just a matter of relinking the tables and making sure I test all the important code parts that use DAO recordsets.

I can't post my database for obvious reasons, but if you want to post your schema, I'll see if I can help.
 

tmyers

Member
Local time
Today, 03:55
Joined
Sep 8, 2020
Messages
370
Understandable. My current work place has no technological system in place. We use a blank excel file that has a template that someone here made probably 18 years ago, and a simple alphabetical paper filing system.

The boss, who younger like me (early/mid 30's) wants a more modern system in place. I had initially tried to revamp our excel form and add in automation and such, but it became too rigid for our needs. Decided Access was better, but my experience is next to none so its been a learning process.

If you would like, I attached my database further up that theDBGuy was working with. You are obviously more than welcome to take a look at it. It probably has the somewhat general structure of the one you referenced (I hope) as my ends are very close to what you showed.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:55
Joined
Feb 19, 2002
Messages
30,132
You have another thread going and I replied there since it seemed to be appropriate. If you would like to see the app I built to get some ideas, I can set up a GTM that you and your boss if he is interested can join. I'm happy to show what I did although I can't just give it away. If your need is close enough in concept to what I've already built, I might be able to talk my client into licensing it to you since you are not a competitor.
 

Users who are viewing this thread

Top Bottom