Full Version: Insert Multiple New Records
UtterAccess Forums > Microsoft® Access > Access Forms
mike60smart
Hi Everyone
I have a Main Form that us used to add New Members named "frmMembershipNumbersNew"
I have a BeforeUpdate Event - Me.txtMemNo = Nz(DMax("MemNo", "tblMembershipNumbers"), 0) + 1
to give me a 1 up MembNo
Is it possible to use an Unbound Textbox to enter a Numerical Value which represents a Number of New Members to be added and
then use a Command Button to run an Append query to create the specific number of Records and enter the One Up MembNo ??
The reason for this is that the Member Numbers are going to be allocated to specific People prior to Data Input and then an Append query will be used to
Insert additional Data at a later date
Your help appreciated
theDBguy
Hi Mike,
Just curious... Where would you be appending from?
mike60smart
Hi theDbGuy
I have created a Form that shows the Next Membership Nr to use by using DMax on the tblMembershipNumbers ( ie show 32)
Othen have a Control on a Continuous Form that Allows me to enter the MembNo's as follows:-
33
34
35
36
I then have a Command Button to Append these MemNo's to the tblMembershipNumbers
I think this will work
J.D.
Besides the member number, are there any "required" fields? Often in my tables for employee's or members in your case the Fname and Lname fields are mandatory (required) which means you will need to dummy in some data to make your idea work while at the same time somewhat side stepping the intension of requiring data in those fields.
lso what happens if the user puts in say 5 mem numbers but then ultimately only 4 get used, what happens to that 5th? who cleans it up and how? Just some things to think through.
Hope this helps,
mike60smart
Hi JD
only need to start the record off by entering a MemNo
The Members then complete an Online form with their personal details and Bank Details
This data is then provided in a Report from the Company who deal with Registrations
The Report which is an Excel file is used to enter the Data for the M
mike60smart
Hi JD
I have My form setup with a list of MemNo's to be appended to tblMembershipNumbers
On the On Click Event this Append query runs
INSERT INTO tblMembershipNumbers ( MembershipNrID, MemNo )
SELECT tblMembershipNumbers.MembershipNrID, tblMembershipNumbers.MemNo
FROM tblMembershipNumbers;
However it errors out with the following error message
Click to view attachment
Is there anything I need to add to the query in regard to criteria to make these records append??
mike60smart
Hi theDBGuy
I am still not getting anywhere with this process
I have modified the Append query so that it does NOT include the Autonumber and it now looks like this@=
INSERT INTO tblMembershipNumbers ( MemNo, GoneAwayID )
SELECT qryAppendMemberNumbers.MemNo, qryAppendMemberNumbers.GoneAwayID
FROM qryAppendMemberNumbers
WHERE (((qryAppendMemberNumbers.GoneAwayID)=2));
I still get the same error message 0 records appended
Any thoughts on how this can be achieved??
Your help appreciated
mike60smart
Hi theDBGuy
modified the append query so that it makes reference to the Form
INSERT INTO tblMembershipNumbers ( MemNo )
SELECT qryAppendMemberNumbers.MemNo
FROM qryAppendMemberNumbers
WHERE (((MemNo)=[Forms]![frmAddMembersNumber]![txtNr]));
Still no Joy ???
theDBguy
Hi Mike,
I am not sure that we have understood what you were trying to do yet. Can you post your table structure and some sample data to better understand what you're trying to achieve? Thanks.
Owonder... Would this other thread be similar to what you're trying to do?
Just my 2 cents... 2cents.gif
mike60smart
Hi theDbGuy
cannot upload the Db as it contains confidential data
The database is for recording details of Club Members who pay an annual subscription
The data was entered manually previously but all payments are now controlled by a Company that deals with all Bank Payments
This has meant that the user now needs to create a New Record for EACH New Member and generate JUST the following fields in the Members table
tblMembershipNumbers
-MembershipNrID = PK
-MemNo
Up to this switch over the MemNo was automatically generated by use of the DMax
The user now users an Online GUI which allows the data input of all the personal and bank details of the New Member together with the MemNo which I need to create
Then an Excel Report is sent back to the User for appending to the speciific Members record using the MemNo to Match the data
So all I am trying to do is Cretae a New Record and the Next MemNo by using a Command Button on an Unbound Form
The Unbound Form currently shows MemNo to be used mext
It has an Unbound Textbox named txtNr into which the user types the Next MemNo
And a Command Button that runs an Append query to Insert into tblMemberNumbers the Next MemNo
Any help appreciated
theDBguy
Hi Mike,
Have you looked at the other thread I posted earlier? Can you get some pointers from it? If you can't post your db, can you at least post some screenshots?
Just my 2 cents... 2cents.gif
projecttoday
Pardon me if I jump in here but why is it that you can't just put the insert in a loop?
J.D.
Mike,
THere is a little Demo that should do the trick for you. I broke it out into a couple of steps using a temp table in case there needs to be some manual adjustment. the finalize also checks to make sure there are no pre-existing records, although the append would fail, it at least give you the options to then show the user which ones are bad (sorry I didn't take it that far).
I'm sure the more query savy folks could streamline a few things but the structure and idea is what I wanted to get across of how I'd do what you describe.
Of course, if the "more data" field as an example is mandatory then the append will result in 0 records added, which was my concern on my first post. Hopefully that isn't an issue for you but if it is, You just need to dummy in some more vaules until the real data is available.
Hope this helps,
mike60smart
Hi JD
You can go to the top of the class
Many thanks works a treat
kisses.gif
J.D.
br />Glad to help!
oodluck with the rest of the project.
mike60smart
Hi JD
Sorry to be a pest
The users have asked if it is possible to add 2 additional fields ?
Otried modifying your code to allow the addition of the 2 fields highlighted in Red below:-
Private Sub btnCalculate_Click()
Dim x As Integer
Dim strSQL As String

For x = 1 To Me.NumAdds
strSQL = "INSERT INTO tblTempAdds ( tmpMemberNumb , tblTempAdds.MemSurname, tblTempAdds.InternetRef ) SELECT " & Me.MaxMemNum + x & ";"
CurrentDb.Execute strSQL
Next x
Me.Requery
End Sub
Private Sub btnFinalize_Click()
Dim strSQLCheck As String
Dim strSQLAdd As String
Dim strSQLDeleteTemp As String
Dim rs As DAO.Recordset


strSQLCheck = "SELECT tblTempAdds.tmpMemberNumb,tblTempAdds.MemSurname, tblTempAdds.InternetRef FROM tblTempAdds INNER JOIN tblMembershipNumbers ON tblTempAdds.tmpMemberNumb = tblMembershipNumbers.MemNo;"
Set rs = CurrentDb.OpenRecordset(strSQLCheck)
If Not rs.EOF Then
MsgBox "At least one of your MemberNumbers conflicts with existing numbers", vbCritical, "Duplicate Member Numbers"
Exit Sub
End If

strSQLAdd = "INSERT INTO tblMembershipNumbers ( MemNo , MemSurname , InternetRef ) SELECT tblTempAdds.tmpMemberNumb, tblTempAdds.MemSurname, tblTempAdds.InternetRef FROM tblTempAdds;"
CurrentDb.Execute strSQLAdd, dbFailOnError

strSQLDeleteTemp = "DELETE tblTempAdds.* FROM tblTempAdds;"
CurrentDb.Execute strSQLDeleteTemp, dbFailOnError

Me.Requery
Me.NumAdds = ""
MsgBox "Member Numbers have been added.", vbInformation, "Complete"

End Sub
When i run this I get the error
Click to view attachment
Where have I gone wrong ???
mike60smart
Hi JD
modified the Event as shown below to allow for the additon of 2 fields :- Surname and InternetRef
Private Sub btnFinalize_Click()
Dim strSQLCheck As String
Dim strSQLAdd As String
Dim strSQLDeleteTemp As String
Dim rs As DAO.Recordset


strSQLCheck = "SELECT tblTempAdds.tmpMemberNumb,tblTempAdds.Surname, tblTempAdds.InternetRef FROM tblTempAdds INNER JOIN tblMembershipNumbers ON tblTempAdds.tmpMemberNumb = tblMembershipNumbers.MemNo;"
Set rs = CurrentDb.OpenRecordset(strSQLCheck)
If Not rs.EOF Then
MsgBox "At least one of your MemberNumbers conflicts with existing numbers", vbCritical, "Duplicate Member Numbers"
Exit Sub
End If

strSQLAdd = "INSERT INTO tblMembershipNumbers.MemNo , tblMembershipNumbers.Surname , tblMembershipNumbers.InternetRef " & _
" SELECT tblTempAdds.tmpMemberNumb, tblTempAdds.Surname, tblTempAdds.InternetRef FROM tblTempAdds;"
CurrentDb.Execute strSQLAdd, dbFailOnError

strSQLDeleteTemp = "DELETE tblTempAdds.* FROM tblTempAdds;"
CurrentDb.Execute strSQLDeleteTemp, dbFailOnError

Me.Requery
Me.NumAdds = ""
MsgBox "Member Numbers have been added.", vbInformation, "Complete"
End Sub
When I run this it errors on the INSERT INTO Line
Can you tell me where I am wrong ???
Your help appreciated
J.D.
Mike,
Based on the error message, my guess would be that you dont' have those field names in both the temp table and the destination table. Or if they are.. check the spelling. I added the fields and made the necessary changes to my demo which is attached.
Hope this helps (and sorry again for the delay, took the weekend and unplugged)
mike60smart
Hi JD
pot on once again
Many Many thanks
hat_tip.gif
J.D.
mike60smart
Hi JD
Sorry to be a pain but your solution has started acting strange
Oenter data a shown below:-
Click to view attachment
When I select Finalize I get this error:-
Click to view attachment
When I select DEBUG it highlights the line :- Me.Requery
When I look at the table that stores the data input it shows that some of the data has been added
Click to view attachment
Any thoughts ???
J.D.
Try adding a
Me.dirty = false
Is the first line in the finalize code. Looks like the last record isn't saving when the button is clicked and that should do the trick.
mike60smart
Hi JD
erfect
Thanks once again
cal-align:middle" emoid=":thumbup:" border="0" alt="thumbup.gif" />
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.