Adding Conditional If statement to change a Auto Number field

[Miked13]

Hello All,

Title is a little confusing. Need to add a character to an auto number with an If statement.

The scenario I am working with is, we have requests coming in through three channels, Emails, Cycle Counts and Recounts. These are identified by who is requesting them. ie MDiGregorio is a cycle count(C). I need to have the txtAuto(Auto number) entered into txtCaseNumber and add the corresponding letter(C,E or R).

DoCmd.OpenQuery qryDuplicateRequest, acViewNormal, acReadOnly
        If (Forms!frmRequest!txtRequester = "MDiGregorio") Then
            tblRequest!Issue Case Number = tblRequest!AutoNumber & "C"
        End If

Thank you

 

[Minty]

You can't edit an autonumber.
Simply store the requester_ID (letter or number) in a separate field, and you won't mess up your Parent Child relationships and cause yourself a whole host of pain further down the line.

 

[Miked13]

Yep, i read that earlier. I'm trying to use nested if statements to take the autonumber into another field and add a letter.

for some reason i cant get the field to be populated

 

[Minty]

There really is no need - see this thread for another lot of reasons why you don't need to do this; https://www.access-programmers.co.uk/forums/showthread.php?t=294940

Remember you can always create a calculated field in a query and display the information like this if you want, just don't store it.

 

[plog]

This sounds like you aren't taking advantage of the 'relational' part of relational database. You don't store data that can be determined in other manners--which this is.

First, you need a table to relate your names to their channel:

tblRequestors
ReqID, ReqName, ReqChannel
1, MDiGregorio, Cycle Count
2, Smith, Email
3, Jones, Cycle Count
4, Marks, Recount

Then in tblRequest you add a ReqID field which will hold the ReqID from tblRequestors. With that you can create a query to determine who requested it and also get their channel. That's how relational database are to work, not by encoding data within other fields.

 

[Miked13]

This would work for a default but I need to have it changed depending on the requester. Can i place a If statement in the Control Source?

 

[Miked13]

Plog, you totally broke my head.

I currently have a requester table being used, but not sure how this would help in this instance.

 

[Minty]

Can you post up a picture of your tables and relationships perhaps with some highlighter around the fields you are trying to tie together?

To try and put what Plog said in more simple terms - It sounds as if you may have a basic table structure design issue, which is why you are trying to do something that you shouldn't need to do.

 

[Miked13]

Here is the relationship table

 

[plog]

First, I nailed it. You need a new table exactly as I described.

Second, here's all the other issues I see with your tables:

1. Improper use of autonumber IDs. tblCount should not store the [FirstName] from tblUserID in [Counter]. That value should be the [ID] field of tblUserID. The primary key in one table goes into the other table as the foreign key (google those 2 terms).

2. Reserved words as field names. [Level] is a special word in access and shouldn't be used as a name. Here's a complete list of them: https://support.office.com/en-us/ar...-symbols-E33EB3A9-8BAA-4335-9F57-DA237C63EABE. Also for those of you playing along at home--[Autonumber] is not a reserved word--I would have got that wrong.

3. Spaces in field names. Just makes coding and querying easier if you eliminate non-alphanumeric characters.

 

[Minty]

Okay that looks a little "Non-Standard"... The below is not criticism, just observations to hopefully get you on track.

You appear to have a link between the UserName in your requester table, and a field called counter in your tblCount? If the Counter field is storing the person doing the count you should be storing their ID not their name. What if they get married and the name changes? Your relationship would be broken.

Your tables tblIssue and tblIssueDetail look strange to me, either one is redundant or that relationship isn't correct.

The link between tblRequst and tblCount looks very wrong they can't have the same primary key field - it means that all the data may as well be in one table? They are directly and uniquely linked after all?

Your CBORequestor field in the Users table doesn't make sense. The user table should only have static user information in it. What purpose does it serve in that table or do you think it should serve?

I think it might be worth describing in plain English - no database terminology - what the Business model is that you are attempting to record and process with this, so we can try and see where you should be heading with your database. Pretend we are 12 years old and know nothing about your business or requirements. That way you will be talking the correct level for us poor keyboard warriors who never get out...

 

[Miked13]

Thanks Minty, I agree with your observation, guess its what happens when you are self taught by youtube and forums. Have a lot of different style code interacting(like a combustion engine made out of duct tape and pottery). I will work on cleaning up the relationships now

The database is being used to monitor and record all inventory counts. The counts are being initiated by requests, so its very similar to an IT Case management system. For the most part it works it only falls apart when i am adding expansions to it.

I was able to solve the issue that started the post. See below:

Private Sub btnEnter_Click()
        If Me.txtRequester.Value = "MDigregorio" Then
            Me.txtCaseNumber = "C" & Me.txtAuto
        End If
            
            If Me.txtRequester.Value = "BJohnson" Then
                Me.txtCaseNumber = "R" & Me.txtAuto
            End If
                
                If Me.txtRequester.Value = "CFast" Then
                    Me.txtCaseNumber = "X" & Me.txtAuto
                End If
                   
                        If Me.txtRequester.Value = "TNiesman" Then
                        Me.txtCaseNumber = "M" & Me.txtAuto
                    End If

However, I am now receiving a mistype issue since i had to change the Issue/Case number from a number field to a Short text. This is the code that needs to be redefined.

DoCmd.SearchForRecord , "", acFirst, "[Issue Case Number] = " & Str(Nz(Screen.ActiveControl, 0))

This code is to have the selected record in a listbox to be the current record viewed on the form.(would prefer to keep as a listbox, even against better judgment).

Any ideas?

 

[isladogs]

Excuse me for joining in here....

I normally use DoCmd.FindRecord & had never heard of DoCmd.SearchForRecord.
The correct syntax is: DoCmd.SearchForRecord(ObjectType, ObjectName, Record, WhereCondition) with all 4 arguments being optional

a) omit empty strings "" on optional arguments
b) you need text delimiters for Issue Case Number as it's a text field
Also it MAY make more sense to use "" rather than 0 in the Nz part

So this gives:

DoCmd.SearchForRecord , , acFirst, "[Issue Case Number] = '" & Str(Nz(Screen.ActiveControl, "")) & "'"

c) Also not sure you need the Str function. You could try

DoCmd.SearchForRecord , , acFirst, "[Issue Case Number] = '" & Nz(Screen.ActiveControl, "") & "'"

Suggest you add error trapping to manage the situation if an empty string is somehow the outcome

I've not looked at the rest of your code as I've only skimmed the thread
Back to Minty...