I have a data entry form (frmInspectMill) with a combo box (cboCoilToUse) on it whose row source is a simple query that is bound to tblCoils.
The SQL is:
Code:
SELECT tblCoils.CoilNumber_PK, tblCoils.CoilNumber FROM tblCoils;
The idea is that the user opens this form to enter various datum including a coil number. The coil number might already exist in tblCoils and it might not.
Right now when I open frmInspectMill, the combo box (cboCoilToUse) shows the coil number of the first record in tblCoils - - which is long ago used up and gone.
How do I have cboCoilToUse open as a blank combo box?
I have two other cbos on this form that both open blank but the row source for both of those are value lists.
Hi Tim. Not sure I understand. Is the combobox bound to a field in your table? If so, when you open the form, does it show the first record? If so, then the combo should display whatever was assigned to that record.
Are you picking a Coil from AvailableCoils? I'm trying to determine what the recordsource is for the Form frmInspectMill . I guess we need a little more details of the activity you are trying to support.
I have a data entry form (frmInspectMill) with a combo box (cboCoilToUse) on it whose row source is a simple query that is bound to tblCoils.
The SQL is:
Code:
SELECT tblCoils.CoilNumber_PK, tblCoils.CoilNumber FROM tblCoils;
The idea is that the user opens this form to enter various datum including a coil number. The coil number might already exist in tblCoils and it might not.
Right now when I open frmInspectMill, the combo box (cboCoilToUse) shows the coil number of the first record in tblCoils - - which is long ago used up and gone.
How do I have cboCoilToUse open as a blank combo box?
I have two other cbos on this form that both open blank but the row source for both of those are value lists.
Then open the form with Data Entry property set to yes and it will open to a blank record? That's the only way I know of that you can open a bound form and not expect a bound control to show data.
It appears to me that you don't want this combobox to show expired items. If that's the case then you need to add an expiry date field to the table the combobox is drawing the data from. Now adjust your query so that it doesn't display anything in the combobox where the date has expired. If a Date doesn't suit, then add a "boolean" a checkbox field so that you can identify components as obsolete, and adjust the query the combobox is based on accordingly...
After reading through all of your posts I've gone back and done some discovery and tried some different mockups including starting a new form from scratch.
When I created the new form I simply selected tblInspectMill and clicked the "Form" icon on the "Create" menu. Then I changed the "CoilNumber-PK" control to a combo box and set its Rowsource to tblCoils; changed the column count to two, and column widths to 0,1.
Then I set the form's Data Entry property to "yes." I also left the primary key id field on the form to be sure that it says "(New)", which it does, so the form is ready for a new record to be entered. And the combo box for the coils shows the first coil number in tblCoils instead of being blank.
Honestly - everything you all have suggested above has made me rethink what my goal really is - especially Uncle Gizmo's question about not wanting to show expired coils.
From experience I can say that we've, between the three of us, actually chosen a coil from the dropdown cbo less than 5% of the time. It does happen but very rarely as we usually burn through coils very quickly. Thus, there is no choosing a coil from the cbo.
The truth is I don't need to show any coils in a combo box at all. I was simply trying to take advantage of the "Not In List" property of the combo box....and I want the control on frmInspectMill to be blank so there is a visual indicator that it needs to be filled.
The reason for this is that other fields in tblCoil are really important for trying to deduce various issues with coatings, suppliers, perforation, etc. If it weren't for that, I would just record the coil number in my mill inspection and move on and have duplicates and it wouldn't matter - but that is not the case.
SO WHAT I REALLY NEED is a means of having the user enter a coil number in some kind of control - whether that be a combo box or a textbox doesn't really matter - and then have some means of automatically checking the entered value against the CoilNumber field in tblCoils. If the value is found in tblCoils then all is good and the actual coil number is placed in the coil number field of the form which is bound to tblInspectMill and the primary ID for the coil from tblCoils is recorded in tblInspectMill. If the entered value is not found then we need a message asking if we would like to enter a new record in tblCoils after which that same value would be entered on the open frmInspectMill.
That's actually what I'm trying to accomplish....and a textbox would work just fine - but I don't believe I get the "Not In List" property that does a lot of the backend VBA legwork for me.
How do I accomplish that?
Is DLookup something I should be learning about?
Or could I use a variation of this code I just stumbled across:
Code:
Private Sub Command200_Click()
If (txtGoTo & vbNullString) = vbNullString Then Exit Sub
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[CoilNumber]=""" & txtGoTo & """"
If rs.NoMatch Then
MsgBox "Sorry, no such record '" & txtGoTo & "' was found.", _
vbOKOnly + vbInformation
Else
Me.Recordset.Bookmark = rs.Bookmark
End If
rs.Close
txtGoTo = Null
End Sub
I'm not entirely clear how to morph the above code but it seems promising as a start.
Then open the form with Data Entry property set to yes and it will open to a blank record? That's the only way I know of that you can open a bound form and not expect a bound control to show data.
It appears to me that you don't want this combobox to show expired items. If that's the case then you need to add an expiry date field to the table the combobox is drawing the data from. Now adjust your query so that it doesn't display anything in the combobox where the date has expired. If a Date doesn't suit, then add a "boolean" a checkbox field so that you can identify components as obsolete, and adjust the query the combobox is based on accordingly...
Hi Uncle Gizmo - I like your idea - - too big of a fish to fry - -and that type pf data (usage, expiration, partial or not) is already recorded in a separate database by our purchasing department. There won't be any linking to them. Inventory questions are nowhere near my scope. Characteristics and material properties of coils - on the other hand - are very much within my scope.
In other words, within my scope of work, it would be a monumental task to try to stay on top of coil inventory.
But your thoughts ave helped me really drill down to what exactly I am trying to accomplish.
Thank you!
Tim
please see post#10 above to see where my head is currently.
Then you have bound it to a field, which is not something that usually should be done. If you accidentally alter the value you have just edited the field. Usually combos are unbound and are used to filter data only. Not saying never, just not usual for experienced db programmers.
You might consider an unbound textbox. Either
a) a button to check if the entered value exists and a yes/no prompt if it doesn't. Downside is that nothing happens until button click or after update
b) a textbox with FAYT (find as you type) code. Not sure what type of form you have for the coil data but I suppose any subform would work.
c) same as b but this might be one of the few times I'd consider a split form. Either way, as value is entered, the records displayed begin to filter
d) FAYT textbox again, but this time it filters a listbox. I think I prefer this one because the danger of free form data entry is in juxtaposition. Enter P123489 when you should have entered P123439 and all of a sudden you have an extra record for the same thing. Count those unique records and now you're looking for a coil that doesn't exist. The listbox would control existing data if you have to recall the record for editing. The filter would reduce scrolling. Not sure how many records are involved or how much of a pain filtering would be, but there's a couple of ideas for you.
I think I know what you're doing as well. I worked for a time in temper mills so some of the terms are familiar. Regarding what I said about bad data, it reminds me that they had a position (job) called coil checker. They often were in large fields of stored coils looking for particular ones for a lineup, but I don't think it was ever because of bad data. Out of curiosity, how large are some of these coils? I recall the largest I worked with were around 58,000 lbs.
Then you have bound it to a field, which is not something that usually should be done. If you accidentally alter the value you have just edited the field. Usually combos are unbound and are used to filter data only. Not saying never, just not usual for experienced db programmers.
Like I said - the only reason I did it was to take advantage of the "Not In List" property that triggers an "Add New Coil" form....and yeah....I can see the "danger" of existing records inadvertently getting changed. Recognizing it is a bad idea.
You might consider an unbound textbox. Either
a) a button to check if the entered value exists and a yes/no prompt if it doesn't. Downside is that nothing happens until button click or after update
If I went this route how would I be handle coils that are "not in list" and have my "Add New Coil" form popup?
Its a popup form not a subform. It should likely stay that way I think - or I suppose I could use a tabbed form but I have never been able to wrap my mind around how to reference controls across multiple tabs in a tabbed subform. I might look into that. I'm really focused on expediency though at the moment.
This would likely offer the opportunity to add a new record immediately if an existing one isn't located - but I'm also not crazy about split forms - primarily because the front end of this db is going "live" on a rugged tablet and I am trying to adhere to a strict economy of screen space.
d) FAYT textbox again, but this time it filters a listbox. I think I prefer this one because the danger of free form data entry is in juxtaposition. Enter P123489 when you should have entered P123439 and all of a sudden you have an extra record for the same thing. Count those unique records and now you're looking for a coil that doesn't exist. The listbox would control existing data if you have to recall the record for editing. The filter would reduce scrolling. Not sure how many records are involved or how much of a pain filtering would be, but there's a couple of ideas for you.
I see the positives of "d" also. Again, what about when the user (me and two other guys) needs to enter a coil that is ont he mill but not in the coil list - meaning we're in the middle of an inspection and need to get prompted to enter a new coil number in tblCoils without leaving the inspection form?
I think I know what you're doing as well. I worked for a time in temper mills so some of the terms are familiar. Regarding what I said about bad data, it reminds me that they had a position (job) called coil checker. They often were in large fields of stored coils looking for particular ones for a lineup, but I don't think it was ever because of bad data. Out of curiosity, how large are some of these coils? I recall the largest I worked with were around 58,000 lbs.
Ahhhhh. Yeah....very similar. You would be "upstream" of what we do. We're a rolling mill operation primarily but also do some fabrication. Lots of airport ceilings, convention centers, public buildings of all kinds.
You have me beat at 58K lbs! Ours top out at 24K. Of course they are not all master coils - they're often split but still not 58K. wow. We do a lot of projects with various types of coated coils. Some are even coated to look like real wood. It's pretty cool.
Regarding the Tab control only, you don't have to worry about which tab a control is on, as far as MS Access is concerned the control is not on a Tab as such, it is just the same as any other control on your form.
However, the control is on a page, the page is its parent, (not the form) and you can ask the question of the control which page are you on? Which might be/is useful.
To answer your question, I was thinking that as you type, whatever you're showing as a "list" (including subform records if any) starts changing as you filter. When it stops filtering and no record is found that matches what you've typed, how you deal with entering that value is a matter of choice. One would be to prompt user to add (yes or no) the entered value. I think a No choice is warranted as they may not have completed the entry. If they say yes, then I don't see the need for a popup form unless there is other related data that is required to be entered at that time - you could simply add the value. Whether or not you then need to requery the form I don't know. Perhaps yes, because you have default data in your design, or when it is loaded the form has 1 or more records on it that would not apply. Another would be a button if you prefer that over AfterUpdate of the unbound textbox.
I don't remember who those 58K coils were for. When they did Ford rewinds, they were typically 55K. As a strapper, I loved those because it took about 30 minutes in between coils. I HATED Sono Tube because they had to be within a couple hundred pounds of 10K and they could knock those off of a 40K coil at the rate of about 1 every 60 seconds.
I have cbo in the header of frmCoilStatus (which is also the "Add New Coil" form) with this code on its AfterUpdate event:
Code:
Private Sub cboMoveTo_AfterUpdate()
Dim rs As DAO.Recordset
If Not IsNull(Me.cboMoveTo) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[CoilNumber_PK] = " & Me.cboMoveTo
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub
When I begin typing in this unbound cbo it starts autofilling until it can't find a record that matches. If it does
And on a different form, frmJobDetails, I have very different code in a cbo in the header that results in basically the same thing (searches while typing and fills the form below):
Code:
Private Sub cboFindJobNumber_AfterUpdate()
On Error GoTo cboFindJobNumber_AfterUpdate_Err
DoCmd.SearchForRecord , "", acFirst, "[Job_ID] = " & Str(Nz(Screen.ActiveControl, 0))
cboFindJobNumber_AfterUpdate_Exit:
Exit Sub
cboFindJobNumber_AfterUpdate_Err:
MsgBox Error$
Resume cboFindJobNumber_AfterUpdate_Exit
End Sub
I understand the former code but I don't really understand the "Nz" in the second piece of code. When typing into either of the aforementioned combo boxes, it appears that autofilling/autosearching is already happening with out any special code specifically meant for that purpose.
But if either one of these pieces of code seems appropriate I am still not completely sure how to utilize them for my stated purpose - especially when the real world conditions mean that we will be typing in a coil number during an inspection and that coil may or may not exist in tblCoils and needs to be added to tblCoils.
It's important to remember that at least currently I would be in the middle of filling out frmInspectMill when I enter a coil number into it and find out that i need frmCoilStatus to open so I can enter the un-recorded coil ("Add New Coil"), close frmCoilStatus, and have the new coil number populate the Coil Number textbox on frmInspectMill.
O apologize if it seems I'm repeating myself. I'm actually trying to work this out as I type this post - and it helps. I have an idea.
I don't remember who those 58K coils were for. When they did Ford rewinds, they were typically 55K. As a strapper, I loved those because it took about 30 minutes in between coils. I HATED Sono Tube because they had to be within a couple hundred pounds of 10K and they could knock those off of a 40K coil at the rate of about 1 every 60 seconds.
That's funny! We don't like small coils either because we have to setup more often and usually waste the first two hundred feet of the head of the coil.
The Nz function is there to protect you, if the the active control contains a Null value. If you try and use the "NULL" you will get an error. However the Nz function converts Null into the 0 (which is shown) you can also return an empty string "" or something else if you want.
I envision that I enter a value, the list starts filtering (assuming there is one - I'm starting to lose track of what you have going on) and when I click or tab out of that field, DLookup looks for the value entered and since it won't be found (if there is list filtering going on and there are no records visible) the fact that the lookup returns no record is what triggers the prompt.
Research 'find as you type'
To learn how to prompt, research the message box function. The DLookup result (0) would be the trigger. The answer is passed back to the function and you do what you need to do based on the returned value of the function.
