Query Data to Table (1 Viewer)

Dirtboy

New member
Local time
Today, 18:45
Joined
Jun 4, 2020
Messages
1
Hi All,

This is my first post in this forum. I've done pretty good working with Access for the past 20 years. Mainly because I didn't get really deep into the program and what it can do. I stuck to basic tables, forms, queries and such.

My issue now is, I have a Work Order database that i created. Most fields I'll either enter by hand or from a combo box on a form and they write to the Work Order table like they should. One of the fields is the Work Order Number and I decided to upgrade this field so it would self-populate. I added a Current Date field that pulls the last two digits of the year for me, I added a field that calculates the Julian Date for me, and I have an Autonumber field that creates the Task Number.

In a query I created a new field that concatenates the two digit year, the Julian Date, and the Task Number. The problem I have is, I can't get that number to write to the main Work Order Table. Can I create a field in the Work Order table that would do the concatenation instead of doing it in a query? If so, how?

Any assistance would be greatly appreciated.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:45
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF!

Since it's a "calculated" value, you really don't need to store it in the table because you can always calculate it again using a query or a form expression.
 

Micron

AWF VIP
Local time
Today, 19:45
Joined
Oct 20, 2018
Messages
3,476
Suggest you just adopt a less complicated method unless there is some reason why you cannot. What would be wrong with an incrementing number? If there are different departments creating the work orders, then the originating department and work order number is a combined index. This is a subject I have some familiarity with and would also advise that it's likely not a great idea to keep the task number in the same table.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:45
Joined
May 7, 2009
Messages
19,169
on multi-user environ, you specify the Work Order as Provisionary.
before saving the new record, check the provisionary work order against
the table if someone has already saved save wo.
if there is, create new one.

example, we save the wo to table1

table1(table)
Task number (autonumber)
wo (string, work order no)
date (date/time, work order creation date)

Code:
public function create_new_wo() as string
dim s as string
create_new_wo= format(date,"yy") & julian() & nextAutonumber("table1", "task number")
end sub

'function julian
public function julian() as string
julian = format(datepart("y", Date), "000")
end function

'next autonumber
' https://stackoverflow.com/questions/6498221/retrieve-next-autonumber-for-access-table
Public Function NextAutonumber(ByVal pTable As String, _
        ByVal pAutonumField As String) As Long

    Dim cat As Object
    Set cat = CreateObject("ADOX.Catalog")
    Set cat.ActiveConnection = CurrentProject.Connection
    NextAutonumber = cat.Tables(pTable).Columns(pAutonumField).Properties("Seed")
    Set cat = Nothing
End Function

generate the "new wo" on the Fom's BeforeInsert event:

private sub form_beforeinsert(cancel as integer)
me!wo = create_new_wo()
end sub

since this is multi-user, anyone can save same wo before you,
so check again if the wo you have was already been used:

private sub form_beforeupdate(cancel as integer)
dim s as string
s=create_new_wo()
if me!wo <> s then
'somebody has used this wo, you are too slow
'then generate new one
if msgbox("WO: " & me!wo & " was already used. replace it with " & s & "?", vbyesno)=vbyes then
me!wo = s
else
cancel=true
end if
end sub
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 18:45
Joined
Feb 28, 2001
Messages
27,001
It is ALWAYS up to you but from a strictly theoretical viewpoint you should never try to concatenate in a table that which is easily created using a query. You surely can use a query for almost any purpose based on reporting because Access just wants a record source. Queries work fine.

For forms, you can do the concatenation on the form rather than in the table. And as a matter of security, you never let people actually see the raw table or query because doing so opens up a window to data mucking. You have no control over datasheet views but you CAN control forms and reports.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 19:45
Joined
Feb 19, 2002
Messages
42,981
You have no control over datasheet views
/That's not quite right. Forms/Reports in DS view have all the same events available that they have in Single or Continuous view.

Dragging a query onto a form/report does not create a subform/subreport so you do not have any control and that makes this very poor practice.
 

Users who are viewing this thread

Top Bottom