Solved Uses Random numbers to generate custom id

Nonnylizing said:
It auto-generated but when I save, record is not seen anywhere
Click to expand...
Hey, one step at a time. Okay? I have to run out but will check back later. Cheers!
 
theDBguy said:
Hey, one step at a time. Okay? I have to run out but will check back later. Cheers!
Click to expand...
Ok.... but then I think you should know that it actually saves new record, both SN and fleetNo in the query but only save SN in the table....the fleetNo field in the table still remain blank
 
Last edited:
if you can generate the fleetNo from SN, then there is No Need for you to have the fleetNo field on that table.
you just use the Query in Report, Form and it will generate Same FleetNo on that query.

you are doing your self a favor because your db will be smaller.
and you only update SN field, rather than both SN, and fleetNo.

imagine if you edit the SN to something else, then you need to
go back and change the FleetNo field using code or SQL query update.
 
Here is another explanation from a different viewpoint.

You know the SN. The other part, "MOT/T/" is constant, according to what you tell us. You would store those six characters every time you create your desired format - but that is a waste of space and Access is optimized towards reducing wasted space. The thing about forms and reports is that they are able to operate from a query as well as a table. This gives you two options.

First, if you use a query like theDBguy showed you in post #7, you can drive reports with that constant added in to a field that you don't have to store every time.

Second, there is such a thing as adding a constant label adjacent to a data field where the constant "MOT/T/" can be displayed next to your serial number, not from the layer of the table or query, but from the layer of the form or report..

In NEITHER case will you need those extra six characters to be stored in the table. OK, you ask, what's the deal about six lousy characters? Why am I catching flak over it?

This is YOUR database and it will go wherever you steer it. But we are trying to explain a basic principle - that constant data doesn't need to be stored in a table, be it six characters or the opening paragraph to War and Peace. And we are bringing it up now because that principle applies to the question you asked.

You will find that we often try to redirect you towards more efficient use of the database. Please take it in that light. Have you ever had a parent, aunt, or uncle try to gently tell you something would be better if you did it another way?
 
The_Doc_Man said:
Here is another explanation from a different viewpoint.

You know the SN. The other part, "MOT/T/" is constant, according to what you tell us. You would store those six characters every time you create your desired format - but that is a waste of space and Access is optimized towards reducing wasted space. The thing about forms and reports is that they are able to operate from a query as well as a table. This gives you two options.

First, if you use a query like theDBguy showed you in post #7, you can drive reports with that constant added in to a field that you don't have to store every time.

Second, there is such a thing as adding a constant label adjacent to a data field where the constant "MOT/T/" can be displayed next to your serial number, not from the layer of the table or query, but from the layer of the form or report..

In NEITHER case will you need those extra six characters to be stored in the table. OK, you ask, what's the deal about six lousy characters? Why am I catching flak over it?

This is YOUR database and it will go wherever you steer it. But we are trying to explain a basic principle - that constant data doesn't need to be stored in a table, be it six characters or the opening paragraph to War and Peace. And we are bringing it up now because that principle applies to the question you asked.

You will find that we often try to redirect you towards more efficient use of the database. Please take it in that light. Have you ever had a parent, aunt, or uncle try to gently tell you something would be better if you did it another way?
Click to expand...
Trust me sir, I need to store them in the main database table....if I can't handle the manipulation, then I'll have to manually input the FleetNo... though it will prolong my entry time....

But then @theDBguy promised he'll do something so I'm patiently waiting for him
 
if you insist, forget about the Query.
bring your form in design view.
add code to Change Event of Serial textbox:
Code: 
Private Sub SerialTextboxName_Change()
    Dim sValue As String
    sValue = Me.SerialTextboxName.Text & ""
    If sValue <> "" Then
        Me.FleetTextboxname = "MOT/T/" & sValue
    Else
        Me.FleetTextboxname = Null
    End If
End Sub

change SerialTextboxname and FleetTextboxname to correct textbox name on your form
Serial textbox:
 
arnelgp said:
if you insist, forget about the Query.
bring your form in design view.
add code to Change Event of Serial textbox:
Code: 
Private Sub SerialTextboxName_Change()
    Dim sValue As String
    sValue = Me.SerialTextboxName.Text & ""
    If sValue <> "" Then
        Me.FleetTextboxname = "MOT/T/" & sValue
    Else
        Me.FleetTextboxname = Null
    End If
End Sub

change SerialTextboxname and FleetTextboxname to correct textbox name on your form
Serial textbox:
Click to expand...
It worked!! and saving too 🌹
Thank u sir... but then I need another manipulations...
In one of my combo boxes, there's one named permit_type with contents
Buses
Tricycle
Taxi
etc
The fleet numbers are assigned according to permit type thus
Buses. "MOT/R/[SN]
Tricycle. "MOT/T/[SN]
Taxi. "MOT/P/[SN]
Where SN is serial number just like we've worked out

Now...is it possible that I select a permit type and the fleetNo corresponds to it
i.e. if I select Buses, the fleetNo textbox autopopulates as MOT/R/[SN] after entry of SerialNo and if I select Tricycle, the fleetNo becomes MOT/T/[SN], etc

I feel like I'm asking us to journey to hell and come back😁
Pls help
 
add another column to your combo and add the T, R, P to their respective row. then change:
Code: 
Private Sub SerialTextboxName_Change() Dim sValue As String 
sValue = Me.SerialTextboxName.Text & "" 
If sValue <> "" Then     
Me.FleetTextboxname = "MOT/" & _
combo.Column(1) & "/" & sValue 
Else 
Me.FleetTextboxname = Null 
End If
 End Sub
 
Nonnylizing said:
It worked!! and saving too 🌹
Thank u sir... but then I need another manipulations...
In one of my combo boxes, there's one named permit_type with contents
Buses
Tricycle
Taxi
etc
The fleet numbers are assigned according to permit type thus
Buses. "MOT/R/[SN]
Tricycle. "MOT/T/[SN]
Taxi. "MOT/P/[SN]
Where SN is serial number just like we've worked out

Now...is it possible that I select a permit type and the fleetNo corresponds to it
i.e. if I select Buses, the fleetNo textbox autopopulates as MOT/R/[SN] after entry of SerialNo and if I select Tricycle, the fleetNo becomes MOT/T/[SN], etc

I feel like I'm asking us to journey to hell and come back😁
Pls help
Click to expand...
Ah, so they were never going to always be MOT/T after all. :(
 
arnelgp said:
add another column to your combo and add the T, R, P to their respective row. then change:
Code: 
Private Sub SerialTextboxName_Change() Dim sValue As String
sValue = Me.SerialTextboxName.Text & ""
If sValue <> "" Then   
Me.FleetTextboxname = "MOT/" & _
combo.Column(1) & "/" & sValue
Else
Me.FleetTextboxname = Null
End If
End Sub
Click to expand...
You said...add column to "your combo"
Which combo pls? Do you mean the Permit_type combo box? If yes, then the source of the drop-down is not from a different table but rather from the "I will type in the values that I want" of the Lookup Wizard....what do I do exactly
 
Last edited:
the combo I am referring is the permit_type combo.
you add In between the list (on the combo's Row Source) "T";"R", etc., eg:

"Buses";"B";"Tricycle";"T";"Taxi";"R"

don't forget to separate them with semicolon (;)
still on the combo's Property->Format
Column Count: 2
Column Widths: 1";0

now change this to:
Code: 
Private Sub SerialTextboxName_Change()
Dim sValue As String
sValue = Me.SerialTextboxName.Text & ""
If sValue <> "" Then     
    Me.FleetTextboxname = "MOT/" & _
    Me.permit_type.Column(1) & "/" & sValue
Else
    Me.FleetTextboxname = Null
End If
End Sub
 
arnelgp said:
the combo I am referring is the permit_type combo.
you add In between the list (on the combo's Row Source) "T";"R", etc., eg:

"Buses";"B";"Tricycle";"T";"Taxi";"R"

don't forget to separate them with semicolon (;)
still on the combo's Property->Format
Column Count: 2
Column Widths: 1";0

now change this to:
Code: 
Private Sub SerialTextboxName_Change()
Dim sValue As String
sValue = Me.SerialTextboxName.Text & ""
If sValue <> "" Then    
    Me.FleetTextboxname = "MOT/" & _
    Me.permit_type.Column(1) & "/" & sValue
Else
    Me.FleetTextboxname = Null
End If
End Sub
Click to expand...
Love you man.... it's working
 
arnelgp said:
the combo I am referring is the permit_type combo.
you add In between the list (on the combo's Row Source) "T";"R", etc., eg:

"Buses";"B";"Tricycle";"T";"Taxi";"R"

don't forget to separate them with semicolon (;)
still on the combo's Property->Format
Column Count: 2
Column Widths: 1";0

now change this to:
Code: 
Private Sub SerialTextboxName_Change()
Dim sValue As String
sValue = Me.SerialTextboxName.Text & ""
If sValue <> "" Then    
    Me.FleetTextboxname = "MOT/" & _
    Me.permit_type.Column(1) & "/" & sValue
Else
    Me.FleetTextboxname = Null
End If
End Sub
Click to expand...
But.....can I ask you one more question?
Pleeeeeeeease 🙏
 
anu tanung mo bro?
 
arnelgp said:
anu tanung mo bro?
Click to expand...
In my Permit type as earlier stated,(Bus, tricycle, mass transit, taxi, etc), Bus and Tricycle are further Divided in to Routes (for bus) and province (for tricycle) as follows;
Buses: R-01, R-02, R-03....R-10 indicating Route 1 to 10
Tricycle: NKW, NSK, UDE, etc indicating abbreviations for the province
Now, In my form, I have combos for RouteNo and Province each

How do I make the route and province reflect in the fleetNo when I select their respective Permit type in the Permit type combo such as

Bus; MOT/R-01/[SN], MOT/R-02/[SN] etc

Tricycle: MOT/NSK/[SN], MOT/NKW/[SN]. etc

..while still maintaining those of Taxi, Mass transit, etc, as they were

I know I should have asked everything earlier but I thought I could solve this on my own after you solved the initial one...mangyaring huwag masaktan 🙏
 
Why is nobody talking to me...
Have committed a crime?
Have violated the forum rules and regulations?
Have I insulted anybody?
Is it a crime that I learned step 1 and then try to achieve step 2 on my own using step1?
Can't somebody try to push for further answers using the one he learnt?
@theDBguy , @The_Doc_Man , @Gasman why are u people letting me suffer like this when i seek for solutions of my problems from you?

I AM SORRY IF MY MANNER OF APPROACH WAS WRONG but I need help on this please...
I promise I'll never trouble this site again if I am helped this last time


God Bless us all
 
You must log in or register to reply here.

Similar threads

C
Invalid use of Me.
Replies
8
Views
297
cheekybuddha
cheekybuddha
E
Emmanuel Katto Dubai : Help Needed with Generating Unique Customer Codes in Access
Replies
8
Views
539
Pat Hartman
P
F
Solved setfocus to field in subform directly
Replies
9
Views
399
The_Doc_Man
The_Doc_Man
F
Custom Sequential Number with Year-Numbers-One Letter
Replies
2
Views
420
Eugene-LS
Eugene-LS
A
Solved Two Combo Boxs for Name & Number
Replies
4
Views
805
accessonly11
A

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom