Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

VBA, VB6, Ms access

Posted on 2010-11-13
10
Medium Priority
?
371 Views
Last Modified: 2012-05-10
Dear experts, i have a from on ms access where i have a button save and save some records in different tables. for this i set different openrecordset. the problem is its saving in all tables except for 1

here it is
Set db = CurrentDb
Set rst = db.OpenRecordset("Player_Details")


With rst
    .AddNew
    !Firstname = Me.Text3
    !Lastname = Me.Text5
    !DoB = Me.Text7
    !Height = Me.Text9
    !Weight = Me.Text11
    !Level = Me.Combo33
    pk_value = ![idPlayer_Details]
    .Update

End With
rst.Close
    

Set db8 = CurrentDb
Set rst8 = db8.OpenRecordset("Player_Occupation")
With rst8
    .AddNew
    !idOccupations = Me.Combo37
    !idPlayer_Details = pk_value
    !NameOfInstitute = Me.Text26
    !Hobby = Me.Text26
    .Update
End With
rst8.Close



Set db3 = CurrentDb
Set rst3 = db3.OpenRecordset("Team_History")
With rst3
    .AddNew
    !idTeam_Details = Me.Combo40
    !idPlayer_Details = pk_value
    .Update
End With
rst3.Close

Open in new window


Player_Details and Team_History are saving successfully. but i dont know why Player_Occupation is not updating.. Please help
0
Comment
Question by:Alv45525
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +4
10 Comments
 
LVL 8

Expert Comment

by:2toria
ID: 34127040
Do you get any error messages at all?  

One hint that might be useful, especially for future readability of your code, is to give your fields descriptive names.  For example, if your team details combobox was called cboTeamDetails it would read much better, especially if you leave this code for a few weeks/months and have to come back at some point to debug.  Other than that I can't see anything particularly wrong.  It is early in the morning here though.

Matt
0
 

Author Comment

by:Alv45525
ID: 34127065
^^ thats for response Matt,

Yes i know i need to work on the name of the variables later. it was just a set up.

Actually theres no error message.

all saves in respective tables except for Player_Occupation
0
 
LVL 8

Expert Comment

by:2toria
ID: 34127078
Can you upload the file for me to have a look at?  It might be easier to check out the problem.

Matt
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 34127234
Hi,

instead of using a recordset you should better use a SQL command for that purpose like this:

DBEngine(0)(0).Execute "INSERT INTO Player_Occupation " & _
                       "           (idOccupations,idPlayer_Details," & _
                       "            NameOfInstitute,Hobby) " & _
                       "       VALUES " & _
                       "           (" & Nz(Me.Combo37,0) & "," & _
                                    Nz(pk_value,0) & ",'" & _
                                    Nz(Me.Text26) & "','" & Nz(Me.Text26) & "')"

Open in new window


(DBEngine(0)(0) is a kind of equivalent to CurrentDB, but it's faster as it is an always existing instance of the currnet database object.)
I assumed in this example that the id fields are numerical and the Text26 is alphanumerical (you need to enclose the value in single quotes in this case). Is it correct that two fields in this case have the value of the same textbox (Me.Text26)?

It's difficult to say anything about the recordset solution without knowing what type of recordset you declared, how your error handling takes place and how your tables look like so I agree with Matt that in such cases it is easier to post the database (an extract without important data of course).

Cheers,

Christian
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 1000 total points
ID: 34127359
i see that you have two "id"  fields    
    !idOccupations = Me.Combo37
    !idPlayer_Details = pk_value

in the table Player_Occupation that look like Foreign keys.(FK)

check the relationship of table Player_Occupation to the tables where the above FK id's are
Primary Keys (PK)
If Referential Integrity (RI) is set in the relationship , those FK must be added first as PK to their respective tables.
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 34127547
In addition to giving your controls meaningful names, bear in mind that there is a difference between the control object itself, and the value from the control, which is what you are assigning to fields in the recordsets.  Sometimes it is helpful to assign the values from the controls to variables, so you can test whether they have the correct data, and examine them in the Watch window or write them to the Immediate window.  Here is a sample (for a Date value):

   If IsDate(Me![txtDate].Value) = True Then
      dteSingle = CDate(Me![txtDate].Value)
   End If
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 34127607
Looking at Player_Occupation

    .AddNew
    !idOccupations = Me.Combo37
    !idPlayer_Details = pk_value
    !NameOfInstitute = Me.Text26
    !Hobby = Me.Text26
    .Update

I do not see anything wrong with this so as long as the add was fine, pk_value should have something. Looks like in Player_Details the column idPlayer_Details has been setup as a AutoNumber. Is this right?

If that is the case and you are going on something the same for Player_Occupation, then is idOccupations a autonumber or are you referencing a combo which is a lookup of occupations? If it is the latter then I assume a value has been selected and you do have an autonumber setup? If there is an autonumber set can you not add something like this to check if one has been generated or not i.e.

debug.print  !myOccupationsAutonumber

Do you use

ON ERROR RESUME NEXT

if so then comment out that line so you can tell if an error occurred or add this after the .Update in Player_Occupation

if err.number <> 0 then
    msgbox "Error " & err.number & " when updating occupations. " & err.description
end if

0
 

Author Comment

by:Alv45525
ID: 34128288
i am referencing a combo which is a lookup of occupations
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34128328
Alv45525,


before anything else,  i suggest that you check what was posted in http:#a34127359





0
 
LVL 75
ID: 34129689
@Bitsqueezer:

As far as DbEngine(0)(0) ... I might suggest you check this out:
http://groups.google.com/group/comp.databases.ms-access/msg/9fe98bb5d7cba5ea?hl=en&

mx
0

Featured Post

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
What do responsible coders do? They don't take detrimental shortcuts. They do take reasonable security precautions, create important automation, implement sufficient logging, fix things they break, and care about users.
The viewer will be introduced to the technique of using vectors in C++. The video will cover how to define a vector, store values in the vector and retrieve data from the values stored in the vector.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question