473,692 Members | 2,375 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

AddRecord issue - Visual Basic with Access

46 New Member
Hi guys,

i tried using the AddRecord command (Record operation - Add New Record) provided by MS Acess but the new record is not been appended to the table but instead it has been overwritten.

I have set the focus for the field under the acNewRec already. By right i shld be able to append the record at the end of the record of the field and nt overwritting the record.

What i want to achieve is to be able to select the value from the combo box corresponding to the table 'Student' and append the record based on what have been selected from the combo box (Predefined list) and the remaining textbox values or is there a possibility to use RecordSet properties to append the record? I do knw about adding record using rs.AddNew.

Any code snippets, guidance or correction of my code or logic is very much appreciated. I have just started to embark on VB nt long ago. Hence, considered a novice learner.

Implementation logic

primary key
SerialCode (Autokey)

Fields
StudentId, subjectcode, course, grade

Combo box
StudentId (predefined list) - S1, S2, S3, S4, S4, S5

AddRecord command (by Acess under operation)
acNewRc
StudentId.SetFo cus
subjectCode.Set Focus
course.SetFocus
grade.SetFocus

code

Private Sub AddRecord_Click ()
On Error GoTo AddRecord_Click
DoCmd.OpenForm "Record"
DoCmd.GoToRecor d, "Record", acNewRec
Combo1.SetFocus 'predefined list
StudentId.SetFo cus 'bound to controlsource studentId
course.SetFocus 'bound to controlsource course
subjectCode.Set Focus
Grade.SetFocus
Exit_AddRecord_ Click:
Exit Sub
Err_AddRecord_C lick:
MsgBox Err.Description
Resume Exit_AddRecord_ Click
End Sub

Private Sub Combo1_AfterUpd ate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Cl one
rs.FindFirst "[SerialCode] = " & Str(Nz(Me![Combo1], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Tks for your assistance.
Jan 12 '07 #1
5 4391
MSeda
159 Recognized Expert New Member
Delete the combo1 after update, it is changing moving to the existing student record. Try opening your form like this:
Expand|Select|Wrap|Line Numbers
  1.  DoCmd.OpenForm "Record" , , , ,acFormAdd
This will open your form in data entry mode already on a new record. You can use the DoCmd.GoToRecor d, "Record", acNewRec to add additional records.

Also I don’t really understand all of the set focus business you have, Setfocus just moves focus to a control it does’nt do anything to the control, post back and tell us what you are trying to do here. I think you probably don’t need any of the setfocus.

In short, as far as I can tell all you really need is:

Expand|Select|Wrap|Line Numbers
  1. Private Sub AddRecord_Click()
  2. On Error GoTo AddRecord_Click
  3.  
  4. DoCmd.OpenForm "Record" , , , ,acFormAdd
  5.  
  6. Exit_AddRecord_Click:
  7. Exit Sub
  8. Err_AddRecord_Click:
  9. MsgBox Err.Description
  10. Resume Exit_AddRecord_Click
  11. End Sub
Jan 12 '07 #2
technocraze
46 New Member
Hi dude,

I cannot call out acFormAdd. btw, what u meant by ....
what i want is to append the record to the table. criteria (selected value from the combo box). Remove duplicate StudentId entry.

Delete the combo1 after update, it is changing moving to the existing student record. Try opening your form like this:
Expand|Select|Wrap|Line Numbers
  1.  DoCmd.OpenForm "Record" , , , ,acFormAdd
This will open your form in data entry mode already on a new record. You can use the DoCmd.GoToRecor d, "Record", acNewRec to add additional records.

Also I don’t really understand all of the set focus business you have, Setfocus just moves focus to a control it does’nt do anything to the control, post back and tell us what you are trying to do here. I think you probably don’t need any of the setfocus.

In short, as far as I can tell all you really need is:

Expand|Select|Wrap|Line Numbers
  1. Private Sub AddRecord_Click()
  2. On Error GoTo AddRecord_Click
  3.  
  4. DoCmd.OpenForm "Record" , , , ,acFormAdd
  5.  
  6. Exit_AddRecord_Click:
  7. Exit Sub
  8. Err_AddRecord_Click:
  9. MsgBox Err.Description
  10. Resume Exit_AddRecord_Click
  11. End Sub
Jan 12 '07 #3
MSeda
159 Recognized Expert New Member
Your going to have to explain what your trying to do more clearly.
You may have some of you terminology confused, append means to add a new record.
Update means to make changes to an existing record.

You say you want to remove the duplicate studentID entry. Does this mean that there should be only one record in the table for each student (no Duplicates) If a record already exists for a selected student do you just want to change the data in that record and not add (append, goto acnewrecord..et c.) a new record?

The code you posted opens the form Report and Goes to a new record, then moves focus around the form without doing anything
Then in the combo after update you move to an existing record based on the selection In the combobox.
Going to a new record when you open the for and then having a combobox move to another record doesn’t make sense.
Jan 12 '07 #4
technocraze
46 New Member
Yes, i dnt want the existing StudentId to appear in the table, that means to filter duplicate. I have set this Yes(No duplicate) undet the design view but it give an error - This will allows duplicate entries in tha table. There are two more issue I am encountering, It would be very appreciated if you can take a look at it.

Title : RecordSet update insert error & sql insert syntax error
Title: Listbox values nt shown in textbox - AfterUpdate event

tks
Jan 14 '07 #5
MSeda
159 Recognized Expert New Member
Sorry for leaving you hanging so long, but let me ask for a little more clarification.
This is what I understand about your form and tables.

Your form is bound to a table (I’ll refer it as ‘Course Table’) with the fields Fields:
StudentId, subjectcode, course, grade.
A combobox on your form gets its rowsource from ‘Student Table’ to fill in the StudentID field in ‘Course Table’.

Like I said giving the command ‘GoToNew’ after opening the form and then having a combobox that returns to an existing record doesn’t make sense to me, but I’m not sure what to tell you because I am still not clear as to what you are trying to do.


When you say
i dnt want the existing StudentId to appear in the table, that means to filter duplicate.
Do you mean that you want this Combobox to only list StudentIDs that do NOT already have an entry in ‘Course Table’ ? (This would mean each student ID could only be associated with one course.)
Could you describe exactly what information 'Course Table' Stores it seems to me that you would want multiple entries for each student in order to list all of the courses they are taking.

Also do you want the form to Find existing records for the Student Selected and edit them or do you want to create new record for the Student Selected?

In a previous post you said:
what i want is to append the record to the table. criteria (selected value from the combo box). Remove duplicate StudentId entry.
This sounds to me like you want to add a new record for a student and at the same time delete any previous so there is only one record. Again doesn't make much sense to me but maybe I'm not understanding what exactly you want your form to do or what type of data your table is storing.

Please post back, this time it shouldn't take me four days to reply. or maybe someelese can offer a better insight.
Jan 17 '07 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

0
3690
by: Ray | last post by:
Date problem. Using non American date formats with Microsoft Access and Visual Basic. I set my regional settings to English(Ireland) or English(United Kingdom) and my date appears in Access as DD/MM/YYYY. However if the day is less than 12 the day then becomes the month e.g. 3 July 2003 is displayed in Visual Basic as 7 March 2003. So if I have two records with different dates as such 6/8/03 and 15/8/03 the first date is 8 June 2003...
8
5672
by: delete table with Visual Basic 6 | last post by:
Dear developer, I have an error message when I try to delete or drop Microsoft Access XP table with ADOX in Visual Basic 6. I use the ADOX.Catalog and ADOX.table to create and delete or drop table within Visual Basic program. If anybody knows how to solve the problem, please give me the solution. I would like to thank you for your attention and response. Best regard, Sanad
25
3723
by: Neil Ginsberg | last post by:
I have a strange situation with my Access 2000 database. I have code in the database which has worked fine for years, and now all of a sudden doesn't work fine on one or two of my client's machines. The code opens MS Word through Automation and then opens a particular Word doc. It's still working fine on most machines; but on one or two of them, the user is getting an Automation Error. The code used is as follows: Dim objWord As...
1
1902
by: nicole bissell | last post by:
Hello, I am working in a new form and still making changes to the visual basic code of the form. Not always, but very often when I try to save my changes in the visual basic window and press the icon, this window closes(without saving) and the whole database, too. I already tried to switch everything in a new form and error occurs again after starting to work in the visual basic code of this specific form not in others and I did...
5
3801
by: Microsoft | last post by:
Hi, I have Visual Basic .net 2003 (Standard Edition) & SQL Server 2000 Developer Edition. When trying to create a connection in the server explorer from the .net IDE I get a number of problems; a.. Under the "Connection" tab, under "1. Select or enter a server name:" when I either select the drop down box or click the refresh button I get an error dialog saying "Error enumerating data servers. Enumerator reports Unspecified error'". The...
10
8200
by: Steve | last post by:
I am trying to create a DLL in Visual Studio 2005-Visual Basic that contains custom functions. I believe I need to use COM interop to allow VBA code in Excel 2002 to access it. I've studied everything I can find on COM Interop and .NET. I've also tried many of the 'Walkthroughs' on the MSDN site relating to COM add-ins, .NET and Office XP but am unable to get even these working in Excel 2002 or Word 2002. I've installed the Office XP...
1
4551
by: praful pathak | last post by:
i i am praful pathak,porbandar i want to develop my own cross tab report in visual basic 6 i know what developed query from ms access but how to coded in visual basic in designing time and how to construct it plz solve this problem query from ms access TRANSFORM Sum(Temp.SumOfS_Qty) AS SumOfSumOfS_Qty
2
2081
by: CAM | last post by:
Hello, I am wondering if someone can give me some pointers. Currently I am using Access 2002 I developed an inventory tracking database, which this database is used in California and in Florida. Unfortunaley Access is not very good as an enterprise wide database, accessing the data from one side of the United States to the other its so slow. About 5 years ago I developed a database using Visual Basic 6 as the front end and Crystal...
0
8544
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9090
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
8810
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8810
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6462
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5821
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
2977
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2242
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
1961
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.