Can someone steer me in the right direction?

[Sam Summers]

Hi Guys,

I have spent the last two days on this one and moved forward a bit from certain searches I have made.

Basically I have to create a unique number by combining two text fields.

You will see in the Table screenshot that I have created a 'Prefix' column in my table that correspond to the SubSection.

So when the user selects the Sub-Section I would like a textbox to display the prefix separately and possibly a second textbox would display a number I have managed to generate with leading zeroes e.g. 004. with the prefix so that the result will be for example CR004

I hope this makes sense?

Many thanks in advance

 

[Minty]

Add the Prefix as a hidden column to your combo box - you can refer to it as

Me.YourComboBoxName.Column(x)

Where x is the column number - They are numbered starting at 0 . You can then create your string value.

 

[Sam Summers]

Hi again Minty,

I bet you are sick of me?

I must be missing something now as I am getting "Invalid use of Null" error?

I am using this code and I bet I have something in the wrong order or something like that?

Private Sub SubSection_AfterUpdate()

Me.ItemNo.Text = Me.SubSection.Column(3)

End Sub

Hold fire - like you said previously "They are numbered staring from 0". I'll just go and try that?

 

[Minty]

And make sure you add the column to the design property of the combobox.
E.g.Column Count 3 or however many you have.

 

[Sam Summers]

This little project is making me ill and increasing the share value of Whisky!!!

Two things happening now:-

Now when I open the 'EnterItem' form even though the Data settings are set to allow new, edits, additions etc. The form is populated with one of the stored records?

And from this code I am seeing the wrong sub-section combobox. i.e. I am seeing the 'DROPSSubSection' rather than the 'TOSubSection' even when my report number is HE-182-TRA-DR-022

Private Sub ReportNumber_Change()

Forms!Main!ReportNo = Me.ReportNumber.Text
Me.Command12.Visible = False

    If Mid([ReportNumber], 8, 3) = "TRA" Then
    Forms!Main!TOSubSection_Label.Visible = True
    Forms!Main!TOSubSection.Visible = True
    Forms!Main!ViewDROPSSubSectionBtn.Visible = False
    Forms!Main!ViewBySubSectionBtn.Visible = True
    Forms!Main!DROPSSubSection_Label.Visible = False
    Forms!Main!DROPSSubSection.Visible = False
    Else
    Forms!Main!DROPSSubSection_Label.Visible = True
    Forms!Main!DROPSSubSection.Visible = True
    Forms!Main!ViewBySubSectionBtn.Visible = False
    Forms!Main!ViewDROPSSubSectionBtn.Visible = True
    Forms!Main!TOSubSection_Label.Visible = False
    Forms!Main!TOSubSection.Visible = False
    End If

End Sub

 

[Minty]

If you are opening the form only to add new records make sure it is set to data entry. Then you will always get a new record.

I can't help think this would be easier if you stored each report with a Unigue Autonumbered Primary key that your user doesn't need to see, along with a code / identifier for the company it was for again maybe hidden from the user,
Each report would then have a field telling you which forms to load and you could build the report number up regardless.

 

[Sam Summers]

The form is et to all those and i am getting an already entered record which i cant for the life of me work out why?

I don't have a clue how to start afresh?

This is the hardest one I have done and I might give up?

Maybe dementia is setting in? :banghead:

 

[Minty]

If it is only set to data entry it will only display a new record. Are you sure your not seeing default values that are being populated?

 

[Sam Summers]

Ok, I think the form is working ok now but still cant get the prefix and number thing working but i'll keep trying tomorrow?

The biggest problem right now is from the code I posted earlier and I CANNOT find out what the answer is so far

From the 'ReportNumber' combobox on the 'SelectReportFrm' in the OnChange Event I have the following code:_

Private Sub ReportNumber_Change()

Forms!Main!ReportNo = Me.ReportNumber.Text
Me.Command12.Visible = False

    If Mid([ReportNumber], 7, 3) = "TRA" Then
    Forms!Main!TOSubSection_Label.Visible = True
    Forms!Main!TOSubSection.Visible = True
    Forms!Main!ViewDROPSSubSectionBtn.Visible = False
    Forms!Main!ViewBySubSectionBtn.Visible = True
    Forms!Main!DROPSSubSection_Label.Visible = False
    Forms!Main!DROPSSubSection.Visible = False
    Else
    Forms!Main!DROPSSubSection_Label.Visible = True
    Forms!Main!DROPSSubSection.Visible = True
    Forms!Main!ViewBySubSectionBtn.Visible = False
    Forms!Main!ViewDROPSSubSectionBtn.Visible = True
    Forms!Main!TOSubSection_Label.Visible = False
    Forms!Main!TOSubSection.Visible = False
    End If

End Sub

In the first line of the code it originally read

 If Mid([ReportNumber], 8, 3) = "TRA" Then

for the report number that was assembled from another form using

Me.ReportNo2.Text = "HE-" & [JobNumber] & "-" & [ReportNo] & "-DR-" & Format([ReportID], "000")

which displays in the 'ReportNumber' as for example - HE-182-TRA-DR-022

It seems like the Mid statement isn't working because the first portion of my code just isn't working at all?

 

[Minty]

You appear to have remove the Me. reference to the form again
If Mid(Me.ReportNumber, 7, 3) = "TRA" Then...

If you type Me. in the code window it engages the Editors Intellisense - which will give you a list of the available items, or properties available. This will help you with your a large amount syntax problems

 

[Sam Summers]

It still doesn't work?

Totally baffled but not surprised.

I once had to give up due to Microsofts bugs so maybe I have to do the same this time?

 

[Sam Summers]

Update:

I have managed to find and fix the main problem which was a conflict from a textbox I had hidden that only displayed the "TRA" portion of my document number, however I was asking it to run code by checking it with my 'mid' statement which of course it would never match and therefore it went straight to the second section of my code.

So now my final dilemma of creating unique 'Text' numbers by combining the 'Prefix' and the self generated number?

I have created a query that does this but now I need to find a way of adding this each time to the 'Item' field?

I have attached a screenshot of my query result

 

[Minty]

Where in the process do you need this to happen - is there a definitive point that you can use?

 

[Sam Summers]

Good morning Minty and thank you,

Its mainly for output like viewing a record or for reports so I am trying out different ways to generate it however possible when the record is entered?

 

[Minty]

To be honest if it can be calculated I would always do exactly that - calculate it.
The reasons for this are many but the most obviuos one is that if anyone ever changes any part of underlying data you don't need to worry about trying to capture that and then update your stored version of the calulation.
You can create a simple function to provide the result you want and include that in your queries for report and forms.

If you want some assistance with that post up the sql of the query you had in the picture.

 

[Sam Summers]

I am racking my brains to get this major and essential component achieved?

The prefix is the current method of tying each item to a sub-section and the auto generated 'text' number combined with the prefix is to prevent any duplication by human error.

Here is the SQL. I have now also included the entire 'TOItem' table fields in a newer version of the query to use for the fields of the 'EnterItem' form to see if I can get it to work that way but the SQL here is just the more basic query

SELECT TOItem.ItemID, TOItem.Ref, TOSubSectionLookup.Prefix, Format([TOItem]![Ref],"000") AS Expr1, [Prefix]+[Expr1] AS Expr2

FROM TOSubSectionLookup 

INNER JOIN TOItem 

ON TOSubSectionLookup.SubSectionRef = TOItem.SubSection;

 

[Minty]

This is Air Code (Made up not tested )and not very efficient but should do what you need. Create a new Module and put this in it; I have made some educated guesses as to where to look info up.

I would normally create a recordset and extract the parts required from that but this is a simple and more obvious way for you to use. If you want to see the other method I can easily so you.

Public Function sRefNo(Item_ID As Long) As String

    Dim sPrefix        As String
    Dim sRef           As String
    Dim sSubRef        As String

    sSubRef = DLookup("SubSection", "TOItem", "ItemID = " & Item_ID)
    sPrefix = DLookup("Prefix", "TOSubSectionLookup", "SubSectionRef = '" & sSubRef & "'")
    sRef = Format(DLookup("Ref", "TOItem", "ItemID = " & Item_ID), "000")

    sRefNo = sPrefix & sRef

End Function

To use it in a query put MyRefString: sRefNo([YourITemIDField])

 

[Sam Summers]

Thanks for your reply Minty.

I have created the module and am trying to work out what to do now?

I have ongoing issues and I am now so confused as I have never had problems like this before!

Keep getting these error messages (as attached) from enter data forms for both types that contain subforms.

Cant find the reason at all?

 

[Minty]

I don't think you have had these problems before because I don't think you've tried to do anything this involved with Access before, however we are all here to help and learn, so rather than focusing on the lots of problems let's look at the one we started with.

Those error messages don't mean a lot without context, please turn Off the error handling in your code and post up the code including where your error is happening.

If it's not code then post up the SQL of the query where your error is appearing. Don't forget that the code I posted was based on me guessing the table names and fields by de-constructing your original query, I may have got it wrong.

 

[Sam Summers]

Ok,

It seems to be happening when the sub-section is selected.

I am confident that its not in the code but I cant see whats wrong in the SQL?

I created another form with a subform from the same table and query (which could be the wrong way to do this anyway?) and I got exactly the same error message?

The reason I used the 'LeadingZeroQry' was because it was the way I managed to achieve the prefix and number I was trying to generate but if I can get your module working then I can eradicate the use of the query?

Here is the SQL of the query:

SELECT TOItem.ItemID, TOItem.Ref, TOSubSectionLookup.Prefix, Format([TOItem]![Ref],"000") AS Expr1, [Prefix]+[Expr1] AS Expr2, TOItem.Area, TOItem.ReportRef, TOItem.Section, TOItem.SubSection, TOItem.Photograph, TOItem.Description, TOItem.Primary, TOItem.Secondary, TOItem.Condition, TOItem.Control, TOItem.Comments, TOItem.Frequency, TOItem.DROPSSubSection, TOItem.[How to inspect], TOItem.[Corrosion Class], TOItem.Item

FROM ReportTbl 

INNER JOIN (TOSubSectionLookup INNER JOIN TOItem ON TOSubSectionLookup.SubSectionRef = TOItem.SubSection) ON ReportTbl.ReportID = TOItem.ReportRef;