Can't add data to form when I press add new record?

[LeighW]

Hi,

I've created 3 forms (claims, arguments and evidence) based on their own specific query which have one-to-one relationships. Within each query I can edit data and add new record no problem there.

One of my forms (the claim form) allows me to add a new record and write within that record. However, the argument and evidence form do not allow me to type when I add a new record.

I am wondering whether it's the link I've made within the claim form to send you to the arguments related to that claim. And the same relationship is with the argument-evidence. The code I've used is below (from the link to form wizard using relationship):

Private Sub Command20_Click()
On Error GoTo Err_Command20_Click
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "Evidence Form"
 
    stLinkCriteria = "[Argument]=" & "'" & Me![Argument] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
 
Exit_Command20_Click:
    Exit Sub
 
Err_Command20_Click:
    MsgBox Err.Description
    Resume Exit_Command20_Click
 
End Sub

In theory I guess this is a one-to-many relationship. So is this creating a new query?

If so is that what is stopping me from adding a new record and how would I get around it as I want it to be editable yet user-friendly?

If not what else could it be?

Thanks for any reply,

Leigh

[NeoPa]

LeighW:
I am wondering whether it's the link I've made within the claim form to send you to the arguments related to that claim.

To help we need technical information. Details of what it is that you're referring to in general terms. Otherwise the question, when paraphrased, comes down to :
I have a problem or two with my forms. They are similar but not exactly the same. What's my problem?

For stupidly easy questions that can (sometimes) suffice, but you're thinking about what you want and designing to suit needs. Typically your questions are likely to need supporting technical information from which to work. It's how such things work.

[LeighW]

Ok I'll try and make it more technical.

The "ClaimNo" is the foreign key which links the two tables within the first query and second query. With these queries I have created two forms. On the first form I used the command button wizard, selected "Form Operations" and "Open Form" this opens the second form and I chose "open form and find specific data to display" choosing the foreign key (ClaimNo). This filters the data specific to that that field.

The code for that is:

Private Sub Command31_Click()
On Error GoTo Err_Command31_Click
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "Argument Form"
 
    stLinkCriteria = "[ClaimNo]=" & Me![ClaimNo]
    DoCmd.OpenForm stDocName, , , stLinkCriteria
 
Exit_Command31_Click:
    Exit Sub
 
Err_Command31_Click:
    MsgBox Err.Description
    Resume Exit_Command31_Click
 
End Sub

The third query and form is linked to the second query via the foreign key "ArgCode". Again I used command button wizard to add new form using specific data which this time is the foreign key "ArgCode"

The second one I posted the code for above but I'll post again:

Private Sub Command20_Click() 
On Error GoTo Err_Command20_Click 
 
    Dim stDocName As String 
    Dim stLinkCriteria As String 
 
    stDocName = "Evidence Form" 
 
    stLinkCriteria = "[Argument]=" & "'" & Me![Argument] & "'" 
    DoCmd.OpenForm stDocName, , , stLinkCriteria 
 
Exit_Command20_Click: 
    Exit Sub 
 
Err_Command20_Click: 
    MsgBox Err.Description 
    Resume Exit_Command20_Click 
 
End Sub 

Like I said before, I can edit data within all queries but not within the second and third form.

I hope that is what you meant by technical

Leigh

[TheSmileyCoder]

First off, its always a good idea to adopt a naming convention (any really) and name as a minimum any controls you use in code. 2 months from now, looking at command20 gives you no idea which of teh buttons that is. A name could be btn_OpenClaims, or cmd_OpenClaims or cmdOpenClaims. Which naming convention you use is of lesser importance, but for your own sakes, please use one!

Now the 2 pieces of code you show simply open a form and filter any existing records to match the stLinkCriteria. Any records created with the form will NOT automatically be linked to match the stLinkCriteria.

I can't understand how your form nor data is setup. What fields are you using? You claim they are linked through ArgCode, yet your code indiates they are linked by ClaimNo or Argument.

What is the general purpose of your application? Whats the main/primary form and table?

[LeighW]

As you can probably tell this is my first database/ first time on an IT forum!

Thank you for the info regarding command code names I will definitely change them.

The general purpose of the database is basically to keep all of the information regarding a company project (can't go deeper than that) in data form so that there are no sifting through large documents or duplicated data. It needs to be largely editable for years to keep up with changes to the project.

The main tables/forms are Claims, Arguments and Evidence with other tables linking to them. They are needed to keep up with regulations. I haven't got one ultimate table although Arguments is likely to be used the most. There are 84 pieces of evidence relating to 29 arguments relating to 4 claims at the minute if you understand my meaning.

Fields within these tables include Name of Claim/arg/evi (text form), Description (memo), ID (number), Code (text), number to linked table i.e. ClaimNo within Arg form, DateModified (Date/time)

I understand my mistake you mentioned, I've corrected now to ArgNo (foreign key) instead of Argument which links the Argument form to Evidence Form.

Basically it looks like I can add a new record to the filtered records within the argument and evidence forms but not when I remove the filter. This would be fine but like you say it won't automatically link it with stLinkCriteria which will be annoying when users that have no idea about access try and add a record.

Also looks like when you add a new record to that filter it deletes the record when you remove the filter.

No idea what to do in relation to adding a new record!

Leigh

[TheSmileyCoder]

There are several ways of setting up forms and subforms, and relate their data, and it all depends on your data model, and user activities.

One way is to use a main form (Example could be a order) with a subform containing order Details. A subform is a form contained within another form, and if the Link Master Field (Primary Key og Main table) and Link Chield field (Foreign Key in subtable) property is set correctly, access will automatically filter the subform for you, and ensure that new records have their foreign key set correctly. This choice is good for something like orders, especially where you have many details (many related entries in subtable)

Another example could be a students database. You might use most of the screen real-estate to show courses the student is taking, but still want to store information about Emergency Contact. Now the emergency contact is not "valuable" enough to take up real-estate on the main form so we create a button to open the frm_EmergencyContact in a seperate window. Now the approach here consists of 2 things to remember. We want to ensure that when the frm_EmergencyContact is opened, that it only contains data relevant to the student in question, and that any new records added are linked to the student. Lets assume our tbl_Student has a primary key (autonumber) PK_Student and our tbl_EmergencyContact has a foreign key FK_Student (Number, Long)

Our button (placed on frm_Student) to open the frm_EmergencyContact could look like so:

Private sub btn_OpenEmergencyContact_Click()
  'Ensure that a emergency contact is only started for a saved student
  If Me.NewRecord Then
    Msgbox "Please save student record before adding Emergency Contact"
    Exit Sub
  End If
  docmd.OpenForm "frm_EmergencyContact",acLayout,,"FK_Student=" & me.Tb_StudentID,,acDialog, me.tb_StudentID
End Sub

This assumes that on frm_Student there is a textbox tb_StudentID bound to field PK_Student.

Note that ,,"FK_Student=" & me.Tb_StudentID applies a filter to the form, to only show those records with the correct foreign key.

Note that acDialog opens the form in dialog mode, so that user must close the form again before continueng.

Finally note that as OpenArgs I pass the value in Me.tb_StudentID.

Now openargs in itself does nothing, so I have to add code to teh emergency contacts form:

Private Sub Form_Open(Cancel as integer)
  'Set the default value of tb_StudentID to match the student in question
  Me.tb_StudentID=me.OpenArgs
End Sub

Any new contact added will now as default have the correct student ID.


I had to resort to this student example as I simply don't understand your application requirements well enough to see what is going on. It is important to identify if you, for a certain set of data always have a parent item. I.e. a emergency contact cannot exists without a student, however a student can exist without having a emergency contact assigned. Therefore the design is likely to be based around the student form.

[LeighW]

Thank you very much The Smiley Coder!

I am currently attempting the second option however I am not sure on what you mean by OpenArgs? Do you mean to enter the code above into the tb_StudentID on the emergency contacts form?

Also wish I had done autonumber for the primary key from the start. Can't edit it now :(

Is there a way of making the ID field become autonumber? Perhaps by adding something to the expression builder in the default value?

Leigh

[NeoPa]

I believe Smiley's second block of code should have the following for line #3 :

Me.tb_StudentID.DefaultValue = Me.OpenArgs

His explanation clearly indicates he understands the difference so I'm assuming it was a brain typo :-)

LeighW:
Is there a way of making the ID field become autonumber?

Typically this is not a problem, so I think we will need to read between the lines again to guess what your problem is. I'm guessing that you have relationships set up between various fields of your tables. With that in place you will be unable to make such changes, but if you remove the relationships; change the design of the data and the data itself to make it consistent; then re-add the relationships to match the new layout, I expect you will be able to make it work.

Whatever you do, be careful of losing the links between the data of different tables.

[LeighW]

Thank you NeoPa,

I'm new to coding. Where would you put the second block of code that Smiley wrote?

I can assure you I'll be out of your hair soon! I'm very grateful for the help thus far!

[NeoPa]

That bit of code would be inserted into the form's module itself. The easiest approach is to open the form for design and show the properties pane. With that showing, and the form itself selected (not any of the controls), scroll down the properties until you find one called 'On Open'. Select the top entry, which is actually a string "[Event Procedure]" then click on the little ellipsis button (...) to the right which will open up the VBA IDE (Integrated Development Environment - where code is developed) and leave you in that particular procedure (It will create a basic stub for you if it doesn't already have one). Paste the code into there, being sure not to add another copy of the code that's already there - the procedure stub.

[LeighW]

Thanks again NeoPa I have that code built into the form and it opens up to the correct fields and adds the relevant ID to the next record.

However when I try and open the form from with the database window it comes up with the message:

Run-Time error '94':
Invalid use of Null

When I press "debug" it highlights the line

Me.tb_StudentID.DefaultValue = Me.OpenArgs

Is that because with that code I can only open the form within the master form or have I done something else wrong?

[NeoPa]

The code, as it stands, only handles the situation where it is opened from the other code, and will crash (as you've found) when opened from the database window.

Try instead (for a little more flexibility) :

Me.tb_StudentID.DefaultValue = Nz(Me.OpenArgs, "")

[LeighW]

That worked a treat!

One final thing, can you add more default values i.e. StudentID and StudentName (memo) so that when you press add new record both of them are already on display after filtering?

I tried adding a "&" or ";" within the code suggested above or adding another block of code but none of that worked.

[NeoPa]

You can certainly do that Leigh, but I couldn't formulate any code from such a loose description.

[LeighW]

Hah OK,

Sticking with the student theme,
When you click btn_OpenEmergencyContact on the Student Form

Private sub btn_OpenEmergencyContact_Click() 
  'Ensure that a emergency contact is only started for a saved student 
  If Me.NewRecord Then 
    Msgbox "Please save student record before adding Emergency Contact" 
    Exit Sub 
  End If 
  docmd.OpenForm "frm_EmergencyContact",acLayout,,"FK_Student=" & me.Tb_StudentID,,acDialog, me.tb_StudentID 
End Sub 

Private Sub Form_Open(Cancel as integer) 
  'Set the default value of tb_StudentID to match the student in question 
  Me.tb_StudentID.DefaultValue = Nz(Me.OpenArgs, "")
End Sub 

The code above opens the Emergency Contact form filtered by tb_StudentID and when you click btn_addnewrecord the filtered tb_StudentID is there by default (all this I'm guessing you already know from the code).

On my Emergency Contacts form I also have the textbox linked to field, tb_StudentName (with the data type, text) which is also found in the master form (Student Form).

Also on that form I have the textboxes linked to fields; tb_EmergencyContactName(text), tb_DescripEmergeContact (Memo), tb_EmergencyContactNo(number), tb_DateModified(Date).

I would like the filter to still be linked by tb_StudentID but when you press btn_addnewrecord (created by the add new record wizard) on the Emergency Contact Form I would like the default StudentID field and StudentName field to show up within the textbox to avoid having to type it in. The StudentID and StudentName fields are linked on the student form i.e. on the student form StudentID: 1 = StudentName: Joe Bloggs.

The rest of the fields on the form I would like left blank when pressing add new record.

Hope that is more helpful.

[NeoPa]

I'm not ignoring you Leigh, I've simply been very busy since you posted so I haven't had time to do the question justice yet. I will though :-)

[LeighW]

No worries! Thank you for the message. I've been working on other things within the database in the mean time anyway. Making it editable and secure yet user-friendly isn't easy!

[NeoPa]

The explanation seems a little awkward, but I guess you want multiple controls on the form to have default values set. Maybe it would help if I showed some generic code that would handle that scenario.

Let's assume we have values to pass to three fields named [A], [b] and [C] and the values are "X", "Y" and "Z". What we would do is pass a string as the OpenArgs parameter that include all of these values separated by a special character (for instance a comma) such as "X,Y,Z". The calling code would then ensure these values are split into the DefaultValues of the relevant controls :

Private Sub Form_Open(Cancel As Integer)
    With Me
        .A.DefaultValue = Split(.OpenArgs, ",")(0)
        .B.DefaultValue = Split(.OpenArgs, ",")(1)
        .C.DefaultValue = Split(.OpenArgs, ",")(2)
    End With
End Sub

I hope that explains the matter clearly.

[LeighW]

Thank you NeoPa, I appreciate the effort you put in for this.

I did however find this: http://allenbrowne.com/ser-24.html
which uses a module to set default values and works across all my forms.

Thank you very much for all your advice with the question. If I could buy you a pint I would but with the upcoming 4 days holiday I'm sure you won't need said pint!

[NeoPa]

Simply put, you can never go too far wrong with Allen Browne. That said, from my understanding of your requirement, it seems the approach outlined there is somewhat overkill and complicated for your requirements (at least as far as I understand them). At the end of the day though, you not only understand your requirements better, you are also the one who decides what suits you best. It's certainly very comprehensive code and I doubt it will lead you too far astray in your project ;-)

Anyway, I feel a pint of Ruddles County coming up (or should that be pouring down?). All good for a very interesting Jubilee break :-)