UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
3 Pages V < 1 2 3 >  (Go to first unread post)
   Reply to this topicStart new topic
> Can I Use A Dcount Function, Access 2016    
 
   
Vector1
post Mar 29 2017, 10:26 AM
Post#21



Posts: 41
Joined: 9-March 17



I see. I really appreciate it. Thank you for all the help!
Go to the top of the page
 
BruceM
post Mar 29 2017, 02:27 PM
Post#22


UtterAccess VIP
Posts: 7,022
Joined: 24-May 10
From: Downeast Maine


Can you post a stripped down (and zipped) copy of the database, just a few records, with sensitive information altered or removed? I can more or less picture the structure, but it will help to know the field names and see a few records worth of sample data. I know you have mentioned several of the field names and so forth, but the information is scattered among several postings, and difficult to keep straight.

I can take a guess without this information, but it is likely to take several more exchanges before we can sort out which field I intended when I guessed or tried to remember a field name.

Here's an example of the difficulty in guessing: In which column of the combo box is the ModelID field? I can see the model number, but not the inner workings of the combo box. I could guess it is the bound column, or Column(0), or something, but if I guess wrong it will be difficult to sort out the problem.

You don't need a zip utility. In My Computer you can navigate to the file, right click >> Send To >> Compressed folder. It would help if you do a compact and repair before zipping (after backing up the database, of course).
Go to the top of the page
 
Vector1
post Mar 29 2017, 04:08 PM
Post#23



Posts: 41
Joined: 9-March 17



Here you go!
Attached File(s)
Attached File  CrateLabelDB.zip ( 62.93K )Number of downloads: 4
 
Go to the top of the page
 
BruceM
post Mar 30 2017, 09:28 AM
Post#24


UtterAccess VIP
Posts: 7,022
Joined: 24-May 10
From: Downeast Maine


Here's what I came up with, based on entering a batch of serial numbers via frmSerialNoCreate. The default date is the current date, but anything can be used.

Note the Query qryBarcodesSN, which combines the 17C etc. prefix with an incrementing number. Note also that I changed tblBarcodes so ModelID is used. The other fields can be brought in as shown in qryBarcodesSN. When working with qryBarcodesSN, keep in mind that changes to any of the fields from tblModelNo will be reflected in all records in the query, and will change the value in tblModelNo. If you display those fields you may want to lock the controls.

Relevant to changing tblBarcodes to using ModelID, I changed the Model combo box on frmSerialNoCreate to use qryModelRow as its Row Source.

I'm not sure how the Rev field is used. Assuming it changes from time to time, and you want the tblBarcodes record to show the Rev at the time the SN was created, you could add a Rev field to tblBarcodes, modify the NewBarcodes procedure to look up the current Rev, and insert it into the Rev field in tblBarcodes as part of the INSERT SQL in NewBarcodes.
Attached File(s)
Attached File  CrateLabelDB_2.zip ( 77.48K )Number of downloads: 2
 
Go to the top of the page
 
Vector1
post Mar 30 2017, 02:21 PM
Post#25



Posts: 41
Joined: 9-March 17



Thank you!
I ran it on the database you had and then added all the features to my original database and I am getting Runtime 13 - typmismatch erorr.
I looked into ADO and DAO options and got rid of ADO from the reference but I am still getting the same error.
Also, the query qryBarcodesSN would not not populate. I get an error "ambiguous name...." error.
Any insight? Attaching both the pics.
Attached File(s)
Attached File  Type13Mismatch.PNG ( 14.92K )Number of downloads: 0
Attached File  qryError.PNG ( 4.88K )Number of downloads: 1
 
Go to the top of the page
 
Vector1
post Mar 30 2017, 04:10 PM
Post#26



Posts: 41
Joined: 9-March 17



So, the form "Serial Number Creation" works perfect....
I am still seeing the same error in opening the query which creates the full serial number...Trying to figure it out...
Let me know if you have any feedback.
Thank you.
Go to the top of the page
 
Vector1
post Mar 30 2017, 04:35 PM
Post#27



Posts: 41
Joined: 9-March 17



I can't thank enough!
I really really appreciate your time and effort...
Everything is working out fine now.
Go to the top of the page
 
BruceM
post Mar 31 2017, 07:29 AM
Post#28


UtterAccess VIP
Posts: 7,022
Joined: 24-May 10
From: Downeast Maine


I was done for the day by the time I received your first reply yesterday. It sounds like you got it sorted out. The ambiguous name error could be because the procedure was in two or more places within the code. A Public procedure should be in a standard code module, not a form or report code module.

I did not test with the existing queries, so they may need some tweaking.

I did a quick demo of a form/subform, where the main form is frmModelNo and the subform is fsubBarcodes. See attached. I used a parameter in qryModelNo to limit records to active models. You could limit the Model records to those with associated serial numbers, or restrict the date range of the serial numbers, or whatever you like.

I need to make an observation/suggestion. In future designs, keep a lookout for repeated data. This would have been simpler to manage, both for the task at hand and most likely for future work on this database, if the Model was just the 5892 portion, with the G2 etc. in related records. Also, the prefix (before the 5892, etc.) seems to be invariable. If so, or if it changes rarely, it may be better to store it separately, and attach it as needed. I often use a tblLocal, with one record and as many fields as are needed to handle repeated data such as facility name, standard prefixes, paths where files are stored, and so forth. It is a simple matter to look up the relevant field, either with a DLookup or a simple function that uses a DLookup function to produce the value:

CODE
Public Function Get Prefix() As String

    GetPrefix = DLookup("Prefix","tblLocal")

End Function


In a query or wherever it is needed:

FullModelNum: GetPrefix() & ModelNum & Suffix

If the prefix is subject to occasional change, that too can be managed quite simply, but I'm not trying to get into all the details here, but rather pointing out the possibilities.

Take a look at some of the UA articles about normalization. There are some UA Wiki articles, and also several articles in the newcomer's reading list. Having struggled, like many developers, with non-normalized designs in some of my earlier attempts, I can say it tends to be simpler if you err on the side of over-normalization than if you have to hammer unnormalized data into a normalized structure. Your structure is OK, and is not likely to get you into trouble, but in some cases may be more work than it needs to be.

Anyhow, glad to help. It was an interesting challenge.
Attached File(s)
Attached File  CrateLabelDB_3.zip ( 83.58K )Number of downloads: 1
 
Go to the top of the page
 
Vector1
post Mar 31 2017, 01:30 PM
Post#29



Posts: 41
Joined: 9-March 17



Hi Bruce,
Can you show me how to update the NewBarcodes procedure to select other fields from tblModelNo and insert into tblBarcodes for example field Rev?
Thank you!
Go to the top of the page
 
BruceM
post Mar 31 2017, 02:39 PM
Post#30


UtterAccess VIP
Posts: 7,022
Joined: 24-May 10
From: Downeast Maine


First, add a Rev field to tblBarcodes if you did not already.

The procedure contains this line:
CODE
  strWhere = _
    DLookup("Mid(Model_No,5,4)", "tblModelNo", "ModelID = " & Model)

Similarly, define a new variable at the top, like:

Dim strRev As String

Then create a new DLookup for the Rev:
CODE
  strRev = _
    DLookup("RevLevel", "tblModelNo", "ModelID = " & Model)

Then later in the code, something like this:
CODE
    strSQL_Insert = _
      "INSERT INTO tblBarcodes (ModelID, Serial_No, Date_CrateLabel, Rev) " & _
      "SELECT " & Model & ", " & intSN & ", " & Format(CreateDate, "\#mm\/dd\/yyyy\#") & ", '" & strRev & "'"


I encourage you to evaluate the code to find out more about why it is doing what it is. This link describes debugging techinques, including a description of setting a break point and stepping through the code. Step through the code and hover the mouse over variables and expressions. Look up the various built in functions (DLookup, Mid, Format, DateSerial, and so forth) and try to see how they relate to the code. That link also describes Debug.Print, which can be very useful in evaluating strings. You can use Debug.Print to write SQL strings to the Immediate code window (the article describes this). From there you can copy and paste into SQL view of a query, then switch to Design view for further study.

You have enough technique to at least attempt to add a field other than Rev, if you like. Rev was a little different to insert than the other fields because it is text, and therefore is surrounded by quotes. Similarly, the dates are formatted to include date delimiters (#).

I'm not trying to discourage you from posting again, but rather saying you know enough to have a go on your own. Just back up the database every time you come up with something new that works. smile.gif

Good luck with the project!
Go to the top of the page
 
Vector1
post Apr 3 2017, 01:50 PM
Post#31



Posts: 41
Joined: 9-March 17



Thank you Bruce...
I reviewed the debugging techniques and found them very useful.
I tried on my own but am having issue with one SQL statement

strSQL_BCode = _
"SELECT ModelID, " & _
"Max(Serial_No) As MaxSN " & _
"FROM tblBarcodes " & _
"WHERE ModelID In " & strSQL_Model & _
"AND Date_CrateLabel Between " & strStartDate & " AND " & strEndDate & " " & _
"GROUP BY ModelID"

From my understanding, the code is pulling modelID, and maximum value of field Serial_No (as MaxSN) from tblbarcodes where ModelID is equal to ModelID in tblMotorNumbers WHERE Mid(Model_No,5,4) = " & strWhere & And Date is between that month...

I can't figure out a way to eliminate date restriction and let the code always pick the maximum value of field "Serial_No"

I tried taking out the line ""AND Date_CrateLabel Between " & strStartDate & " AND " & strEndDate & " " & _"
But it am getting duplicate values. Somehow code is starting the Serial_No, for example, from 2253 even though, for example, model_no 11E8249G1 has that serial_no already in the system.
Can you help?
Go to the top of the page
 
BruceM
post Apr 4 2017, 08:18 AM
Post#32


UtterAccess VIP
Posts: 7,022
Joined: 24-May 10
From: Downeast Maine


QUOTE
I can't figure out a way to eliminate date restriction and let the code always pick the maximum value of field "Serial_No"

I thought you said you wanted the serial numbers for each model (that is, the numeric middle of the model number) to start from 1 each month.
QUOTE
am getting duplicate values.

It could be because the highest value was the same in two or more months.

You may have to post the current copy of the database, along with a description of exactly what you are trying to do. As I said, all along I have been under the impression that the SN starts from 1 each month.
Go to the top of the page
 
Vector1
post Apr 4 2017, 09:01 AM
Post#33



Posts: 41
Joined: 9-March 17



I apologize if I said that the serial numbers should start over every month.

The serial numbers should increment whether the month change or the year.

xxxx5892xx should increment e.g. 17D0001, 17D0002, 17E0003, 17F0004,......
xxxx5893xx should increment e.g. 17D0001, 17D0002, 17D0003, 17D0004, 17E0005, 17E0006, 17F0007

I attached the same database you sent back to me. You can see that the 115E5892G5 and 115E5892G7 have duplicate late four digits of the serial number. I would like the serial number to increment for same model type e.g. xxxx5892xx.
Attached File(s)
Attached File  Capture.PNG ( 24.5K )Number of downloads: 2
 
Go to the top of the page
 
Vector1
post Apr 4 2017, 09:02 AM
Post#34



Posts: 41
Joined: 9-March 17



Here is the database
Attached File(s)
Attached File  CrateLabelDB_3.zip ( 85.28K )Number of downloads: 2
 
Go to the top of the page
 
BruceM
post Apr 4 2017, 09:04 AM
Post#35


UtterAccess VIP
Posts: 7,022
Joined: 24-May 10
From: Downeast Maine


QUOTE
The serial numbers should increment whether the month change or the year

What's the difference? If the year changes, the month does also.

I haven't looked at the database, but based on the sample data most of the records are April, but two are May. I don't know what happened to 0001 and 0002 for May, but assuming it is because of records not shown in the sample, the duplicate SNs you describe are for two different months. My understanding had been that the numbers are supposed to start from 1 each month, so there will be duplication of the last four digits. To put it another way, 0001 will occur in every month a SN was entered for a model type (that's what I'm calling the middle numbers such as 5892).
Go to the top of the page
 
Vector1
post Apr 4 2017, 09:44 AM
Post#36



Posts: 41
Joined: 9-March 17



I think the code worked ok and incremented the serial numbers for 115E5892G5 but the code duplicated the serial#s with 115E5892G7, which is what I am trying to avoid.
NO duplication among same type models 5892 and NO restarting serial#s every month.
Go to the top of the page
 
BruceM
post Apr 4 2017, 09:52 AM
Post#37


UtterAccess VIP
Posts: 7,022
Joined: 24-May 10
From: Downeast Maine


Do the serial numbers ever restart for a model type (5892, etc.)?

If not, maybe the problem was just that the string lacked delimiters (quotes). Try this:

CODE
  strSQL_Model = _
    "(" & _
      "SELECT ModelID " & _
      "FROM tblModelNo " & _
      "WHERE Mid(Model_No,5,4) = '" & strWhere & _
    "') "


If so, that was my doing. The Mid function produces a string (text) value, and as such it should be delimited properly.
Go to the top of the page
 
Vector1
post Apr 4 2017, 10:17 AM
Post#38



Posts: 41
Joined: 9-March 17



And I think the reason is that the code selecting ModelID which is unique for each model.
e.g modelId=42 modelNo=115E5892G5
e.g modelID=56 modelNo=115E5892G7
Go to the top of the page
 
Vector1
post Apr 4 2017, 10:25 AM
Post#39



Posts: 41
Joined: 9-March 17



Worked Perfectly!
Thank you
Go to the top of the page
 
BruceM
post Apr 4 2017, 10:26 AM
Post#40


UtterAccess VIP
Posts: 7,022
Joined: 24-May 10
From: Downeast Maine


Great! Just to be clear, is it your expectation that there is no chance of as many as 10,000 serial numbers for a model type?
Go to the top of the page
 
3 Pages V < 1 2 3 >


Custom Search
RSSSearch   Top   Lo-Fi    16th December 2017 - 09:19 PM