creating a new record in a subform

[Damob9K]

Hello all,

I am pretty much a beginner in Access, although I did create a few basic databases with access 97 a long time back !
I am trying to create a database to manage IT assets, most of the structure is done, I'm now trying to get the details done.

The issue I am currently trying to tackle is to get a control button on one form to create a new record in the sub form that sits in the main form via another pop-up form.
So I have :

>PeripheralsViewForm - that has 4 control buttons (Edit Quantity, Edit Description,Add an Item and Exit)

>> PeripheralsSubForm - (Datasheet) which is referenced to PeripheralsQuery

The Edit Quantity and Edit Description open a separate form which allows the user to update only the quantity or description respectively (I have done it this way rather than allowing edits in the data sheet as I feel that it is too easy to hit a key incorrectly and overwrite data in the datasheet view)
Both of these functions work perfectly (to my amazement) - I am using the id field in the subform to link to the pop-up form which then has unbound text input boxes, which I then write back to the subform.
e.g from edit button on click event: DoCmd.OpenForm "perquantform", , , " ID=" & Form.PeripheralsSubForm!ID
and then from the pop-up form "perquantform" on the "Exit & Save" button I have : Forms!peripheralsViewForm.PeripheralsSubForm.Form!Quantity = Me.newquant
DoCmd.Close acForm, "perquantform", acSaveYes

This allows the user to select the record in the datasheet form and then click either the change quantity or description button.
I watched a tutorial that did the same thing by putting a edit button next to each record, but that looked naff to me !
Also also the other reason I did it this way was because I just couldn't get the subform to requery when I had the two edit popups linked to the table or query.

The problem now is that when I click on the "Add an Item" button, it overwrites whatever record is currently selected !!
The code I have is this:
From the "Add an Item" button on click action:
DoCmd.GoToRecord , Forms!peripheralsViewForm!PeripheralsSubForm.Form!, acNewRec
DoCmd.OpenForm "addperform"

And from the form "addperform" - "Save & Exit" button on click action:
Forms!peripheralsViewForm.PeripheralsSubForm.Form!PerType = Me.pertypedrop
Forms!peripheralsViewForm.PeripheralsSubForm.Form!PerMake = Me.permakedrop
Forms!peripheralsViewForm.PeripheralsSubForm.Form!PerModel = Me.newmodel
Forms!peripheralsViewForm.PeripheralsSubForm.Form!PerDescription = Me.newdescription
Forms!peripheralsViewForm.PeripheralsSubForm.Form!Quantity = Me.newquantity

DoCmd.OpenForm "PeripheralsViewForm"
DoCmd.Close acForm, "addperform"

--------------------------------------

the line "DoCmd.GoToRecord , Forms!peripheralsViewForm!PeripheralsSubForm.Form!, acNewRec" is what I have added to try to solve the overwrite issue, but when I run this it comes up with the error :
"runtime error 2498 - An expression you entered is the wrong data type for one of the arguments"
I have put this line in the "addperform" exit action and it comes up with the same sort of error.

Please could one of you experty types tell me what I am doing wrong (other than attempting to write a database with very little knowledge !)

And yes I know that my naming conventions are rubbish !, once I really know what I am doing I will probably rewrite the whole thing, but for now I just need it to work as I can't manage the department that I have taken on with multiple unlinked and non standardized Excel spreadsheets :banghead:

So if anyone can help me I would be eternally grateful !
Kind regards to all

Damian

Sorry should of said . . Using Access 2010

 

[JHB]

You have a "!" to much in the line.

DoCmd.GoToRecord , Forms!peripheralsViewForm!PeripheralsSubForm.Form[B][COLOR=Red]![/COLOR][/B]  , acNewRec

No comments to the whole setup!

 

[Damob9K]

Thanks for the reply JHB, but that didn't make any difference ! Same error.

The odd thing that I have just noticed is that when I re-type that whole line in,
Access auto-corrects the subform name i.e. PeripheralsSubForm.Form! but not the first part: Forms!peripheralsViewForm! which should autocorect with a capitol P, as if it's not finding the main form, but the button is on the main form ??

What does the error "An expression you entered is the wrong data type for one of the arguments" mean ? when I click on the help button it just takes me to a generic MS help page with no links to Access error codes.

Thanks

DB

 

[Damob9K]

OK so realising that this is not the best way of going about adding a new record, I have changed things back to how I had them first which is:
My "Add a New Item" button on the main form opens up the "addperform" which now has the record source set to the Peripherals table, and all of the combo boxes and text boxes set to the respective control source.

This works fine as far as adding a new record does not overwrite the selected record in the subform, BUT ... when I save and exit out of the data entry form (the main form with the subform were not closed, just sit in the background), I can't then seem to get the subform to refresh (requery).
I have created a test button that runs the requery function :
Me.PeripheralsSubForm.Requery - which works perfectly when clicked.

But I can't seem to get this to work on any of the on action functions.
I have tried putting it in the on click action of the "Save & Exit" button on the data entry form, but this does nothing, and and I have tried putting it on the on update action of the main form (and have tried all of the options for syntax for subforms that I have found on the internet)

This is driving me mad !! . . . Help !!

 

[RainLover]

Damob

You have supplied a lot of information for a complex problem.

What we should be looking at is a little info for one small problem.

Do you simply have a Main record, from which you either want to edit an existing sub record or add a new sub record.

You should be able to go very close to writing this without Code.

BTW I do not have 2010 so I am limited in the help I can give. I much prefer 2003. I would think that every computer and every database in your network has been converted to 2010 and tested. If not, then you do not have a standard operating environment. You should have one. Just a bit of additional advice.

 

[Damob9K]

Hi RainLover (btw if you like rain that much you should move to the UK )

Yes sorry my first post is sort of irrelevant now as I have ditched that approach as I realised that it was the wrong way to go about it even though the reason I tried doing it that way was because I couldn't get the subform to refresh.

I'll try to lay out the details a bit better now (sorry I am not up on all of the terminology as yet)

The peripherals table consists of 6 fields (ID, PerType, PerMake, PerModel, PerDescription, and Quantity)
There are no other tables or records that reference it, other than 2 small tables used for combo-boxes used in the data entry form.

The main form used to view or edit the data in that table is called "PeripheralsViewForm" which has 4 functioning buttons (Edit Quantity, Edit Description, Add an Item, and Exit to Main Screen)
It then has a subform nested in it called "PeripheralsSubForm" which itself is referenced to "PeripheralsQuery" and this is in the datasheet view so I can just scroll through the data within it, then click either edit button to edit that record (which works fine)
I am not sure if it needed to be pointing to the "PeripheralsQuery" or just to the "PeripheralsTable" ?

The "Add a New Item" button opens a data entry form called "addperform" with the control source set to the "PeripheralsTable" and uses two tables for two combo-boxes within the "addperform" (which also works fine"

So basically when I exit out of the "addperform" form I want the subform "PeripheralsSubForm" to refresh to show the newly entered item.
I have tried all combinations of : [Me.PeripheralsSubForm.Requery] [Forms!PeripheralsSubForm.Requery] [Forms!PeripheralsViewForm.PeripheralsSubFormcontrol.Form.Requery]
On as many event types I can and none of them work, the only thing that works is the test button I created that just has "Me.PeripheralsSubForm.Requery" on the on click event . . this is what I don't understand . . why does the button correctly requery the subform, but the commands above don't !!

Sorry again if that is a whole load of text that you don't need, but I don't honestly know how to answer your (probably simple) question of "Do you simply have a Main record, from which you either want to edit an existing sub record or add a new sub record." as I am not sure what this means !!

To answer your last comment: Access databases are not supported officially in our environment (although we have Access as part of the Office package) we use 90% Oracle and possibly 10% MS SQL and I would have to get financial approval and put a project proposal to our software dev team, and that would take months and I probably wouldn't get approval for it anyway - hence I am just doing it myself - don't you just love Government red tape

Thanks for your help so far, I hope that makes a little more sense now !

regards

Damian

 

[bob fitz]

The "Add a New Item" button opens a data entry form called "addperform" with the control source set to the "PeripheralsTable" and uses two tables for two combo-boxes within the "addperform" (which also works fine"

Does the user need to go back to the main form before this form is closed.

 

[Damob9K]

Hi Bob,

No the user does not need to go back to the main form until the data entry is completed.
I just have the data entry form popping up over the top of the main form, so when that closes the main form is still there.

Cheers

D.

 

[bob fitz]

Hi Bob,

No the user does not need to go back to the main form until the data entry is completed.
I just have the data entry form popping up over the top of the main form, so when that closes the main form is still there.

Cheers

D.

In that case, in the line of code used to open the form, you could make the form open in Dialog mode, which would stop the code running at that point.
If you the put the following line in the Got Focus of the combo box, the new entry should be in the list:
Me.ActiveControl.Requery

 

[Damob9K]

Eek ... Sorry Bob you lost me there, we may have a crossed wire here, which is most likely of my doing

I'm not sure what you mean when you talk about the combo boxes, as they are working correctly, it's when all the fields on the data entry form are filled out and I press save and exit, the form behind does not refresh.

see screenshot...

just pausing for lunch

cheers
D

 

[bob fitz]

Sorry, my mistake. Just getting confused with different posts.
The line of code would be something like:
Me.PeripheralsSubForm.Requery
which would be placed after the line of code that opens the form in Dialog mode.

 

[Damob9K]

Holly bat doodar that did it !!! Thank you sooooo much Bob.

Just going to take a moment or two to work out the logic of why that works that way !!

Next job is to add data entry validation and null detection to all my input forms.
Reading about IF, is null and nz commands right now.

Thanks again Bob.

 

[bob fitz]

Glad to be of help
Let us know if you get stuck again

 

[JHB]

Thanks for the reply JHB, but that didn't make any difference ! Same error.

I read you've solved the problem, so this is only for clarified.
Sorry I didn't test your code, I just pointed out the most obvious and it was the sign "!".

...
What does the error "An expression you entered is the wrong data type for one of the arguments" mean ? when I click on the help button it just takes me to a generic MS help page with no links to Access error codes.

It does mean in this case, DoCmd.GoToRecord expected at text string as parameter, but you gave it an object = Forms!peripheralsViewForm!PeripheralsSubForm.Form
Even if you had put it between quotation marks that would not have helped anyway, because you first have set focused to the sub form, like the below code.

    Forms![peripheralsViewForm]![PeripheralsSubForm].SetFocus
    DoCmd.GoToRecord acActiveDataObject, , acNewRec

 

[Damob9K]

Hi JHB,

Thanks for that explanation regarding the GoToRecord command, that makes sense now that I see it in front of me.

I'm enjoying this learning process

Hopefully once this blessed database is up n running I'll be able to take the time to learn VBA from the beginning and start a new project !!

Cheers for all the help so far . . I'm going to need some more soon !

D.