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.

 

[lameid]

I'm thinking if you run a query with the sql from your record source, you will find it returns 0 records. If you can't add records, and no records exist, there is nothing to display.

 

[Klepdog]

With the form properties window for the control source I entered "select LOCALMAN.* from LOCALMAN". Table LOCALMAN contains approximately 50 records. On this form I prefer the user not be allowed to add a new record until he or she has clicked a save record button. What I would like to be able to do is not allow an addition until then. Would it be best not to set the record source until then and not worry about allowadditions at all or have it available on form open?

 

[lameid]

Bound forms, those with a recordsource, save the record when a save button is clicked, the record selector is clicked or you leave the record... I guess you could disable the record selector and cancel the changes on form close... You'd still have the problem of saving when changing records... you might be able to cancel those changes on current but never tried it myself but...

Generally, if you want to only save when a button is pushed, it suggests you want to either use an ubound form and code the populating of controls and the saving from them OR at least bind the form to a disconnected recordset. Both are kind of lengthy topics which are bound to have articles or longer threads already. My preference of those two is the unbound recordset as I feel I have more control as everything has to be done in code explicitly but more work.

 

[MajP]

I think you can fake all of this without resorting to unbound forms. It is a little confusing on what you want to happen when the form opens and then what you want to to with the save button. Is it a "save" button or really a button to allow the user to "add" a new record. Please be a little more specific on how you want the user interface to work.

 

[Klepdog]

I gave up using the allow additions control. I finally settled on setting the recordsource as a select from statement during form open then bound the fields to the recordsource. Then when the user was ready to save the new record using a docmd.runcommand.accmdsaverecord (Create the new record). What I have been afraid of is the creation of blank records or the modification of an existing record. During testing i have found both occuring. I created this form with a limited number fields, six with five that must have data, to only create new records with the required data. I am probably doing everything wrong and it is very fusterating(sorry about the spelling). I can not even figure out how to force upper case on specific fields. At times I feel like telling my boss to give this job to someone who knows what they are doing.

 

[MajP]

Use the form's before update event. Here you can verify the data. Tou can cancel within this event to stop from saving the record. You may want to use the new record property in this event to check if it is a new record. In the after update event is where you can do changes like making uppercase

The trick is to not trying to save a record, this is automatic. You need to write the code to not allow saving of invalid records.

 

[MajP]

http://msdn.microsoft.com/en-us/library/office/ff822421.aspx

See examples

 

[lameid]

If you only want to add records, you might want to set "Allow Edits" to no... this will prevent opening existing records in the form.

Force upper case... You can likely do this with an input mask but I would use After Update event to set the control to the UCASE of itseself...

So if you had a control named "txtFirstName"...

Private Sub txtFirstName_AfterUpdate()
      Me!txtFirstName = Ucase(Me!txtFirstName)
End Sub

BTW I'm adding to and partially giving another option for what MAJP has suggested.

 

[Klepdog]

How do you move to a new record, if the new record property shows false? I turned the navigation bar off to help prevent the user from modifing an existing record.

 

[MajP]

Again, it would be real helpful if you can explain exactly how you want it to function. Also is this a single form view or continuous form view?

 

[Klepdog]

A simple 6 field form that only creates new records. The record source for this form is on form load "select LOCALMAN.* from LOCALMAN". Two command buttons, One to save the new record and one to close the form. No scroll bars or record navigation bar. This is a single form. One field is a control field (no duplicates allowed) that is required. Before update the form needs to check to ensure it is a new record, and make sure the control field is not a duplicate. It also must check to ensure the the user entered data into the four remaining required fields. After update clear every field and setup for the next new record. When the user is done then close the form.

 

[TheAceMan1]

... if I set me.allowadditions = false all [blue]fields disappear[/blue] on form open. Set to True then all [blue]fields appear[/blue].

The [blue]Allow Additions[/blue] property controls the new data entry record. This is the last record wether single or continuous form view. The action is to make this record visible for additions ... hidden otherwise.

Since your form is in single view, you should see records on opening of the form (unless you have code that selects the new record line) and no new record if the property is false. Since new data entry can only occur with the new data entry record, this record has to be visible. You could still use your button but there are other ways to trigger saving a record.

Note there is an [blue]Data Entry[/blue] mode where all you is add new records. You may want to conider that.

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

See Ya . . .

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

 

[MajP]

If that form is used for nothing but adding and that is the way you want it to operate then this would be a good candidate for an unbound form using an insert statement.

Option Compare Database
Option Explicit

Private Sub btnCancel_Click()
  DoCmd.Close acForm, Me.Name
End Sub

Private Sub btnOK_Click()
  Dim flds As String
  Dim Vals As String
  Dim StrSql As String
  If RecordExists(Me.Last_Name, "[Last Name]", "Employees", True) Then
    MsgBox "Record already exists"
    'Clear Fields
    ClearFields
    Exit Sub
  End If
  'validate Fields
  If Trim(Me.Last_Name & " ") = "" Then
    MsgBox "Must fill in Last Name"
  ElseIf Trim(Me.First_Name & " ") = "" Then
   MsgBox "Must fill in first name"
 ElseIf Trim(Me.City & " ") = "" Then
   MsgBox "Must fill in the city"
 Else
   ' All checks passed
   'Get field names
    flds = insertFields("Last Name", "First Name", "City")
    Debug.Print flds
    'Get the value part of an insert statement
    Vals = insertValues(SqlText(Me.Last_Name), SqlText(Me.First_Name), SqlText(Me.City))
    Debug.Print insertValues
    StrSql = createInsert("Employees", flds, Vals)
    Debug.Print StrSql
    CurrentDb.Execute StrSql
    MsgBox "Record added"
    ClearFields
 End If
End Sub

Public Sub ClearFields()
  'Edit your fields here
  Me.Last_Name = Null
  Me.First_Name = Null
  Me.City = Null
End Sub

'----------------Do not edit below here

Public Function insertFields(ParamArray varfields() As Variant) As String
  Dim fld As Variant
  For Each fld In varfields
    If insertFields = "" Then
      insertFields = "([" & fld & "]"
    Else
      insertFields = insertFields & ", [" & fld & "]"
    End If
  Next fld
  If Not insertFields = "" Then
    insertFields = insertFields & ")"
  End If
End Function
Public Function insertValues(ParamArray varValues() As Variant) As String
  Dim varValue As Variant
  For Each varValue In varValues
    If IsNull(varValue) Then varValue = "NULL"
    If insertValues = "" Then
      insertValues = "(" & varValue
    Else
      insertValues = insertValues & ", " & varValue
    End If
  Next varValue
  If Not insertValues = "" Then
    insertValues = insertValues & ")"
  End If
End Function
Public Function SqlText(VarItem As Variant) As String
  If Not IsNull(VarItem) Then
    VarItem = Replace(VarItem, "'", "''")
    SqlText = "'" & VarItem & "'"
  End If
End Function
Public Function SQLDate(varDate As Variant) As String
     If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
        Else
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
        End If
    End If
End Function
Public Function createInsert(tableName As String, flds As String, Vals As String) As String
  createInsert = "INSERT INTO " & tableName & " " & flds & " VALUES " & Vals
End Function
Public Function RecordExists(Value As Variant, fieldName As String, tableName As String, Optional IsTextField As Boolean = False) As Boolean
  'Valtype is either Date, Numeric, or String
  Dim recCount As Integer
  If IsTextField Then Value = "'" & Value & "'"
  recCount = DCount(fieldName, tableName, fieldName & " = " & Value)
 MsgBox recCount
  RecordExists = (recCount > 0)
End Function
[/code}

 

[MajP]

If that form is used for nothing but adding and that is the way you want it to operate then this would be a good candidate for an unbound form using an insert statement.

Option Compare Database
Option Explicit

Private Sub btnCancel_Click()
  DoCmd.Close acForm, Me.Name
End Sub

Private Sub btnOK_Click()
  Dim flds As String
  Dim Vals As String
  Dim StrSql As String
  If RecordExists(Me.Last_Name, "[Last Name]", "Employees", True) Then
    MsgBox "Record already exists"
    'Clear Fields
    ClearFields
    Exit Sub
  End If
  'validate Fields
  If Trim(Me.Last_Name & " ") = "" Then
    MsgBox "Must fill in Last Name"
  ElseIf Trim(Me.First_Name & " ") = "" Then
   MsgBox "Must fill in first name"
 ElseIf Trim(Me.City & " ") = "" Then
   MsgBox "Must fill in the city"
 Else
   ' All checks passed
   'Get field names
    flds = insertFields("Last Name", "First Name", "City")
    Debug.Print flds
    'Get the value part of an insert statement
    Vals = insertValues(SqlText(Me.Last_Name), SqlText(Me.First_Name), SqlText(Me.City))
    Debug.Print insertValues
    StrSql = createInsert("Employees", flds, Vals)
    Debug.Print StrSql
    CurrentDb.Execute StrSql
    MsgBox "Record added"
    ClearFields
 End If
End Sub

Public Sub ClearFields()
  'Edit your fields here
  Me.Last_Name = Null
  Me.First_Name = Null
  Me.City = Null
End Sub

'----------------Do not edit below here

Public Function insertFields(ParamArray varfields() As Variant) As String
  Dim fld As Variant
  For Each fld In varfields
    If insertFields = "" Then
      insertFields = "([" & fld & "]"
    Else
      insertFields = insertFields & ", [" & fld & "]"
    End If
  Next fld
  If Not insertFields = "" Then
    insertFields = insertFields & ")"
  End If
End Function
Public Function insertValues(ParamArray varValues() As Variant) As String
  Dim varValue As Variant
  For Each varValue In varValues
    If IsNull(varValue) Then varValue = "NULL"
    If insertValues = "" Then
      insertValues = "(" & varValue
    Else
      insertValues = insertValues & ", " & varValue
    End If
  Next varValue
  If Not insertValues = "" Then
    insertValues = insertValues & ")"
  End If
End Function
Public Function SqlText(VarItem As Variant) As String
  If Not IsNull(VarItem) Then
    VarItem = Replace(VarItem, "'", "''")
    SqlText = "'" & VarItem & "'"
  End If
End Function
Public Function SQLDate(varDate As Variant) As String
     If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
        Else
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
        End If
    End If
End Function
Public Function createInsert(tableName As String, flds As String, Vals As String) As String
  createInsert = "INSERT INTO " & tableName & " " & flds & " VALUES " & Vals
End Function
Public Function RecordExists(Value As Variant, fieldName As String, tableName As String, Optional IsTextField As Boolean = False) As Boolean
  'Valtype is either Date, Numeric, or String
  Dim recCount As Integer
  If IsTextField Then Value = "'" & Value & "'"
  recCount = DCount(fieldName, tableName, fieldName & " = " & Value)
 MsgBox recCount
  RecordExists = (recCount > 0)
End Function

 

[MajP]

So if you had a numeric field and a date field you would do something like this
flds = insertFields("Last Name", "First Name", "City","NumberOfChildren","BirthDate")
Vals = insertValues(SqlText(Me.Last_Name), SqlText(Me.First_Name), SqlText(Me.City), me.NumberofChildren, sqlDate(Me.birthdate))

 

[TheAceMan1]

Klepdog . . .

Note that a form in single form view with [blue]Data Entry[/blue] mode ... doesn't show previously saved records. You can only enter and save new records. When saved all fields are automatically cleared for the next new record entry. All in all it could save you alot of code, hassle and time.

Take a few minutes to check it out and see if you like it ... and to set [blue]Data Entry[/blue] mode see the [blue]Data Entry[/blue] property on the Data tab.

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

See Ya . . .

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

 

[Klepdog]

One field is a date field and is formatted as short date. The control field (called in the table as [LCLID]) is set as text, but I was wondering if I could set as number. The required input is four numbers then a dash followed by four more numbers. What do you suggest. The rest of the fields are text. The code lameid suggested where should I put it? In the fields property beforeupdate?

 

[Klepdog]

TheAceMan1,,,

I will check the Data Entry mode out.

Klepdog

 

[MajP]

So if you want to do it bound. Set the form to data entry. Remove the navigation buttons.

This will allow you to add a new record, hit the ok and move to another new record.
If you add an existing record it prompts and then clears that record
If you cancel it closes the form.
The trick is in the set the me.dirty = false (comitting the record) and then moving to the next new record in the after update event.

Private Sub btnCancel_Click()
  Me.Undo
  DoCmd.Close acForm, Me.Name
End Sub
Private Sub btnOK_Click()
  Dim flds As String
  Dim Vals As String
  Dim StrSql As String
  If RecordExists(Me.Last_Name, "[Last Name]", "Employees", True) Then
    MsgBox "Record already exists"
    ClearFields
    Exit Sub
  End If
  'validate Fields
  If Trim(Me.Last_Name & " ") = "" Then
    MsgBox "Must fill in Last Name"
  ElseIf Trim(Me.First_Name & " ") = "" Then
   MsgBox "Must fill in first name"
 ElseIf Trim(Me.City & " ") = "" Then
   MsgBox "Must fill in the city"
 Else
  Me.Dirty = False
 End If
End Sub
Public Sub ClearFields()
  Me.Last_Name = Null
  Me.First_Name = Null
  Me.City = Null
End Sub
Public Function RecordExists(Value As Variant, fieldName As String, tableName As String, Optional IsTextField As Boolean = False) As Boolean
  'Valtype is either Date, Numeric, or String
  Dim recCount As Integer
  If IsTextField Then Value = "'" & Value & "'"
  recCount = DCount(fieldName, tableName, fieldName & " = " & Value)
  RecordExists = (recCount > 0)
End Function

Private Sub Form_AfterUpdate()
Me.Last_Name = UCase(Me.Last_Name)
Me.Recordset.MoveLast
End Sub