Fields disappear on form open 3

[Klepdog]

I am using Access 2007 on a Windows XP computer. On a form open if I set me.allowadditions = false all fields disappear on form open. Set to True then all fields appear. Data source is set to me.recordsource = "select LOCALMAN.* from LOCALMAN". All fields are bound.

 

[Klepdog]

Thanks MajP. I will try both ways out tomorrow at work.

 

[Klepdog]

MajP,

I tried both ways today at work. First I tried the DataEntry Mode. No matter what I did with this code and attempt it kept modifying an existing record. Next I tried the coding for unbound fields. On the first attempt through I was getting a runtime error of 3134. I went back through the code and found several typos. Fixed the typos and got rid of the 3134, but then I got a 3346. I have not figured out what is causing this one, but the debug.print flds and insertValues showed the following. I never got the debug.print StrSql

flds gave this ([LOCALIDent],[TECHORDERAUTHOR], [PARTNUMBER],[NOMENCLATURE],[DATEADD],[INITIALADD],[Remarks])

insertValues gave this "INSERT INTO ([LOCALIDent],[TECHORDERAUTHOR],[PARTNUMBER],[NOMENCLATURE],[DATEADD],[INITIALADD],[Remarks])VALUES (2014-0013')

Here is some of the code that I entered exactly as shown below

Else
flds = insertFields("LOCALIDent", "TECHORDERAUTHOR", "PARTNUMBER", "NOMENCLATURE", "DATEADD", "INITIALADD", "Remarks")
debug.print flds
Vals = insertValues(SqlText(Me.LCLID), SqlText(Me.Tech_Order), SqlText(Me.Part_Number), SqlText(Me.Nomenclature1), SqlDate(Me.Date_Add), SqlText(Me.Initials), SqlText(Me.Remarks1)
debug.print insertValues
StrSql = createInsert("LOCALMAN", flds, Vals)
debug.print StrSql
CurrentDb.Execute StrSql





Public Function insertValues(ParamArray varValues() as Variant) as String
Dim varValue as String
For Each varValue in varValues
[indent]If IsNull(varValue) Then varValue = "NULL"[/indent]
[indent]If insertValues = "" Then[/indent]
[indent][indent]insertValues = "(" & varValue[/indent][/indent]
[indent]Else[/indent]
[indent][indent]insertValue = insertValue & ", " & varValue[/indent][/indent]
[indent]End If[/indent]
Next varValue
If Not insertValues = "" Then
[indent]insertValues = insertValues & ")"[/indent]
End If
End Function

Can you see what I did wrong? The Public Function RecordExists() works. It lets me know if the Local Id is in a previous record. From what little I can tell the insertFields() function seems to work but things stop at the function insertValues. I still as yet determine if the rest of what I entered is correct.

 

[Klepdog]

Alright, I found two more typos. Fixed them and the code worked flawlessly. Now I have another question. On another form that updates data, it finds the record to be updated but I did not set up a way to save the change to the record. If I use the command button wizard to create a save record button, does it save the record as the current record or does create a new record? I also have a field in the table that currently is set as a text field. The format is yyyy-nnnn with yyyy being the year and nnnn numbers (0001,etc..). Does this field need to stay as a text because of the dash or can it be set as an integer?

thanks Don

 

[MajP]

Fields should only be set as numeric if you plan or could do calculations on them, IMO.
So a stock number, or SSN, or telephone number should be stored as text. They have only numeric characters, but there is no meaning in adding, subtracting, avg... Also if you need to do things like find by year or count the number of items by year, the better design would have been two fields.
A date field and a numeric field. Then in a query you could put the two of them together for display purposes.

Year([YourDateFied]) & "-" & [YourNumericField] as YourNewFieldName

 

[lameid]

If I use the command button wizard to create a save record button, does it save the record as the current record or does create a new record?

The Save record button from a wizard saves the current record for a BOUND form. I have NOT been following closely but I think you went to an unbound form here unless this is a separate form.

I agree with MajP on using separate fields for the year and number portion and using text in general, although numbers are documented to sort and join faster. If hypothetically speaking (I think you are in a different scenario), you were separating month and day, I would use a date/time field and use formatting and if necessary an input mask (I avoid them). If, again hypothetically, I need month and year, typically I just force the day to 1 and store as a date. This is because there is extra functions to get to a date to use things like date functions such as dateadd and datediff... Ultimately though, what you do should be influenced on how you use the data. Somethings that come to mind to consider (some mentioned above) Are you joining and sorting on it? What kind of display are you doing? Will it upset the users to have two separate boxes? To be clear text is easier generally to deal with in this scenario. However, sometimes other considerations might make a difference.

 

[Klepdog]

To answer your first question. Yes this is a new form. I tried the command button wizard to save the change, but I did not like the fact that it did not reset the fields to "". So, I used code to do the docmd.runcommand.accmdsaverecord to accomplish the save change and then clear the fields with the me.field = "". Yes, this is a bound form.

The second question needs a little background. My company policy requires that when we make a new tool or piece of equipment for our use to assign a company ID number. This ID number consists of NGLOCALID(YYYY-NNNN), the YYYY = the current year and the NNNN = current sequence number(0001, 0002, etc...), with NNNN recycling back 0001 at the start of a new year. Currently I have the local id field set to YYYY-NNNN as a text field. The reason behind my question was I thinking maybe I could have the program give the Quality Control people an automation of assigning the next local Id for them or they could input a different number if necessary. Unfortunately I have no idea on how to do this and was willing to experiment. My thought was to break the field from one to two separate fields(one a date field for the year and the other an integer) and go from there. A dash needs to be between the YYYY and then NNNN. Any suggestions?

 

[Klepdog]

Also adding to the second question, since this program will have zero records when I give it to the Quality Control people. The year field in the above idea would have to be changeable as they will have to enter manually all of their old data into this program. Currently all of their records are on paper and this was why I was tasked to create the program. On all of the forms and reports that I have created the text "NGLOCALID" is just a text label with the localid field butting up to it.

 

[lameid]

I would definitely use two fields. Access as an "autonumber" datatype that by default starts with one and increments by 1. This would work well for your NNNN... Autonumbers do not reset... so if you enter a record and delete it because it was a mistake the next one is 2. Also numbers do not store leading 0's, so you would always have to format it or store as text... A lot of options... also something to consider is whether this is the way it has always been done... Does that mean it needs to continue?

For your year in general I would set a default value of Year(Date())... but for old data, I'd recommend a combobox...

Now back to your saving... When you save you are still on that record. It seems like you want to save then add a new record... what it sounds like you are doing is saving the record and changing it to blank without saving it...

 

[Klepdog]

Two questions then.
1. How would you setup a combobox for year(date) so it could go back say 12 years so they could enter all the old paper records. Would I stll need a seperate year field for when they enter new stuff or just use the combobox if it can bring up the current year.

2.I thought with autonumbers you could not reset back to one. I need that ability.

As far as the form that I want to save the change to record. I set all forms that change the record to go to a blank record that I made me.id =1 after a change. I did not want the user to mistake the data from the record they just saved with the next record to be editted. Ergo I blank the fields and force a new search for the next record to be editted. The blank record through the use of code cannot be editted or deleted.

 

[lameid]

Comboboxes can limit to list or not (a property). If you limit to list, whatever the recordsource is, the entry must mach one of those options... The Rowsource can be a value list you type in on the property or Table/query that has the options in it. I tend to use tables or queries for rowsources unless it is a short static list like yes or no (probably would use a check box) or units such as g or kg.

You can programmatically set the Rowsource for a value list or programatically add data to tables. I can't really give pros or cons but like I said I tend to use tables.

The double key thing... An option would be to store the information from paper for lookup purposes and go with a different scheme. On paper it may be difficult to add lots of information but on screen it is generally easier. There are probably more options here than is worthwhile posting. It is more code anytime you move away from using an autonumber for an incrementing key and record locking becomes more of a problem in a shared system. But it can be done. That said, a strong requirement will help.

Your saving... Unless you are doing something I am unaware of, I don't think it is doing what you think it is doing.
Look at your table, enter a record in your form, save it, look at your table, enter another record in your form, save it and look at your table again.

 

[TheAceMan1]

Howdy Klepdog . . .

I've been hanging in the background and have watched this thread grow into a number of complications. No matter what you do I see you need the following ... prompted by the following quote:

[blue]What I have been afraid of is the creation of blank records or the modification of an existing record.[/blue]

If you add the following code to the [blue]On Current[/blue] event of the form you can achieve just that:

[blue]   If Me.NewRecord Then
      Me.AllowEdits = True
   Else
      Me.AllowEdits = False
   End If
[/blue]

... you can view previously saved records ... but only edit new ones.

[blue]Your Thoughts? . . .[/blue]


See Ya . . .

Be sure to see FAQ219-2884 Worthy Reading!
Also FAQ181-2886 Worthy Reading!

 

[lameid]

My last post, the first line says recordsource and should read rowsource. Also consider AceMan's post... I hadn't noticed there is a new record property... I like it.

 

[Klepdog]

If you add the following code to the On Current event of the form you can achieve just that:


CODE
If Me.NewRecord Then
Me.AllowEdits = True
Else
Me.AllowEdits = False
End If

... you can view previously saved records ... but only edit new ones.

This problem was solved when I followed MajP suggestion to use unbound fields and modify the code MajP gave me for my form. On the AddNewEquip form I never wanted the user to see existing records. This form from the start was only to add new tools/equipment. I have also been working on two other forms that allow the user to edit existing records that use the me.recordsource during the forms coding. On those two forms I was able to set me.allowaddition = false and and set me.allowedits to true. I am extremely grateful to MajP for this.


I have been working with lameid on how to use a date field and a integer field for the nglocalid as an automation for my QC people when they enter all of the records that are on paper to the database during the last few posts. Here is what I came up with. It is probably Rube Goldberg approach like MajP likes to tell me. Hopefully You guys can help me make it simpler and more correct as far as coding goes. I have tested the code in the AddNewEquip form (again Unbound)and it does work. I am running this code on the Form_Current()sub routine and after the user saves a new record. Even though the field IDYear is a date field, I do not care what month or day that was entered, only the year is what I was concerned with. So I used a bogus month and day in the code. I did not make the IDNumber field an auto-number as the ID field is already auto-numbered and is the Prime Key. One thing I would like to do is pass both variables myDate and useDate to the Save_Record() Sub routine so that I can save either one of them as needed.

Private Sub Form_Current()
GetNGLocalId
End Sub

Public Sub GetNGLocalId()
Dim db As DAO.Database
Dim rs1 as DAO.Recordset
Dim rs2 as DAO.Recordset
Dim myDate as Date
Dim CurYear as Variant
Dim UseYear as Variant
Dim UseDate as Date
Dim lastId as Integer
Dim nextId as String
Dim ActId as String
Set db = CurrentDb
myDate = Date
CurYear = Year(myDate)
Set rs1 = db.OpenRecordset("select LOCALMAN.* from LOCALMAN where year([IDyear]) = " & CurYear
rs1.MoveLast
If rs1.EOF Then
[indent]nextId = "1"[/indent]
else
[indent]lastId = rs1!IDNumber[/indent]
[indent]nextId = lastId + 1[/indent]
End If
ActId = GetIdNum(nextId)
response = MsgBox("Next I number is NGLOCALID" & CurYear & "-" & nextId &".  Do you wish to use it?",vbYesNoCancel)
If response = vbYes Then
[indent]Me.yrId = CurYear[/indent]
[indent]Me.NumId = nextId[/indent]
ElseIf response = vbNo Then
[indent]useYear = InputBox("Please enter the year of the new record.")[/indent]
[indent]If useYear = "" Then[/indent]
[indent][indent]Exit Sub[/indent][/indent]
[indent]End If[/indent]
[indent]Set rs2 = db.OpenRecordSet("select LOCALMAN.* from LOCALMAN where Year([idyear]) = " & useYear)[/indent]
[indent]If rs2.EOF Then[/indent]
[indent][indent]nextId = "1"[/indent][/indent]
[indent][indent]ActId = GetIdNum(nextId)[/indent][/indent]
[indent][indent]useDate = DateValue("January 01," & useYear)[/indent][/indent]
[indent][indent]Me.yeId = useYear[/indent][/indent]
[indent][indent]Me.NumId = nextId[/indent][/indent]
[indent]Else[/indent]
[indent][indent]rs2.MoveLast[/indent][/indent]
[indent][indent]lastId = rs2!IDNumber[/indent][/indent]
[indent][indent]nextId = lastId + 1[/indent][/indent]
 [indent][indent]ActId = GetIdNum(nextId)[/indent][/indent]
[indent][indent]useDate = DateValue("January 01," & useYear)[/indent][/indent]
[indent][indent]Me.yeId = useYear[/indent][/indent]
[indent][indent]Me.NumId = nextId[/indent][/indent]
[indent]End If[/indent]
[indent]rs2.Close[/indent]
Else
Exit Sub
End If
rs1.Close
db.Close
End Sub

Public Function GetIdNum(varId As String) As String
' This will make the NNNN portion of he NGLOCALID number to have 4 digits for display
If Len(varId) = 1 Then
[indent]varId = "000" + & varId[/indent]
ElseIf Len(varId) = 2 Then
[indent]varId = "00" + & varId[/indent]
ElseIf Len(varId = 3 Then
[indent]varId = "0" + varId[/indent]
End If
End Function

Thanks respectfully

Don

 

[PHV]

Why not simply this ?

Public Sub GetNGLocalId()
Dim UseYear As Variant
Dim nextId As Long
Dim ActId As String
Dim response As Integer
UseYear = InputBox("Please enter the year of the new record.", , Year(Date))
If UseYear = "" Then
    Exit Sub
End If
nextId = 1 + Nz(DMax("IDNumber", "LOCALMAN", "Year(IDyear)=" & UseYear), 0)
ActId = Format(nextId, "0000")
response = MsgBox("Next I number is NGLOCALID" & UseYear & "-" & ActId & ".  Do you wish to use it?", vbYesNoCancel)
If response = vbYes Then
    Me!yrId = UseYear
    Me!NumId = nextId
End If
End Sub

Note the use of the InputBox, Nz, DMax and Format functions.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Klepdog]

Thanks PHV,
I still want to give my QC people the option of the current year IDNumber first (i.e. 2014-0009 if the last id for this current year was 0008), before a different year is asked for. Why the use of a long instead of an integer for nextId. What is the difference between the two data types? Will it return a nextId of 0001 if there are no records for the year inputted? I am also trying to figure out how to pass a date variable "useDate" from a public sub to a private sub so that it can be stored in the record.

 

[PHV]

give my QC people the option of the current year
UseYear = InputBox("Please enter the year of the new record.", , [highlight]Year(Date)[/highlight])

Will it return a nextId of 0001 if there are no records for the year inputted?
nextId = [highlight]1 + Nz[/highlight](DMax("IDNumber", "LOCALMAN", "Year(IDyear)=" & UseYear), 0)

how to pass a date variable "useDate"
You already have useYear in the yrId control on your form.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Klepdog]

That is what I needed to know. Thanks PH. Just curious what is the difference between a long integer and an integer?