Access MySQL

L

Len

The funtion below worked when it was running on the Access Database but
fails now.
I have linked the tables from the MySQL Database using the same names as
they were before.
The form above this code can see the information in the tables this code is
under the save button.

Can anyone help me in changeing this to work with the MySQL database.

Private Sub Save_Click()
On Error GoTo Err_Save_Click

Dim db As
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim l(13) As Long
Dim s1 As Single
Dim t(7) As String
Dim d1 As Date


Set db = CurrentDb()
Set rs1 = OpenRecordset("Supply", dbOpenDynaset)
Set rs2 = OpenRecordset("Invertory", dbOpenDynaset)

d1 = Me![Date]
l(2) = Me![AuctionID]
l(3) = Me![TrollyNo]
t(1) = Me![Location]
l(4) = Me![CustomerID]
t(2) = Me![DocketNo]
l(5) = Me![ProductID]
t(3) = Me![ColourID]
t(4) = Me![g1] & Me![g2] & Me![g3]
t(5) = Me![Size] & Me![Combo50]
t(6) = Me![UnitType]
l(6) = Me![UnitLots]
l(7) = Me![Lots]
l(8) = Me![MinBuy]
l(9) = Me![MinPrice]
l(10) = Me![StartPrice]
l(11) = Me![Weight]
s1 = Me![Com]


With rs1
..MoveLast
l(1) = (rs1![SupplyID] + 1)
.AddNew
rs1![SupplyID] = l(1)
rs1![CustomerID] = l(4)
rs1![DocketNo] = t(2)
rs1![SupplyDate] = d1
rs1![AuctionID] = l(2)
rs1![ProductID] = l(5)
rs1![ColourID] = t(3)
rs1![Grade] = t(4)
rs1![Size] = t(5)
rs1![UnitType] = t(6)
rs1![UnitsLots] = l(6)
rs1![Lots] = l(7)
rs1![MinBuy] = l(8)
rs1![MinPrice] = l(9)
rs1![Weight] = l(11)
rs1![Com] = s1
.Update
.MoveLast
Me![SupplyID] = rs1![SupplyID]
..Close
End With

With rs2
.AddNew
rs2![SupplyID] = l(1)
rs2![AuctionID] = l(2)
rs2![ClockNo] = 1
rs2![Trolly] = l(3)
rs2![Location] = t(1)
rs2![CustomerID] = l(4)
rs2![SupplyDate] = d1
rs2![ProductID] = l(5)
rs2![ColourID] = t(3)
rs2![Grade] = t(4)
rs2![Size] = t(5)
rs2![UnitType] = t(6)
rs2![UnitsLots] = l(6)
rs2![Lots] = l(7)
rs2![MinBuy] = l(8)
rs2![MinPrice] = l(9)
rs2![StartPrice] = l(10)
rs2![Weight] = l(11)
.Update
..Close
End With

Me![Copy].SetFocus
Me![Save].Visible = False

Dim stDocName As String

stDocName = "R_OfferSlid"
DoCmd.OpenReport stDocName, acNormal



Exit_Save_Click:
Exit Sub

Err_Save_Click:
MsgBox Err.Description
Resume Exit_Save_Click

End Sub
 
L

Len

Sorry "This is the wrong way to post"
What do you mean..

Len


GVaught said:
Answered in another area. This is the wrong way to post.

Len said:
The funtion below worked when it was running on the Access Database but
fails now.
I have linked the tables from the MySQL Database using the same names as
they were before.
The form above this code can see the information in the tables this code is
under the save button.

Can anyone help me in changeing this to work with the MySQL database.

Private Sub Save_Click()
On Error GoTo Err_Save_Click

Dim db As
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim l(13) As Long
Dim s1 As Single
Dim t(7) As String
Dim d1 As Date


Set db = CurrentDb()
Set rs1 = OpenRecordset("Supply", dbOpenDynaset)
Set rs2 = OpenRecordset("Invertory", dbOpenDynaset)

d1 = Me![Date]
l(2) = Me![AuctionID]
l(3) = Me![TrollyNo]
t(1) = Me![Location]
l(4) = Me![CustomerID]
t(2) = Me![DocketNo]
l(5) = Me![ProductID]
t(3) = Me![ColourID]
t(4) = Me![g1] & Me![g2] & Me![g3]
t(5) = Me![Size] & Me![Combo50]
t(6) = Me![UnitType]
l(6) = Me![UnitLots]
l(7) = Me![Lots]
l(8) = Me![MinBuy]
l(9) = Me![MinPrice]
l(10) = Me![StartPrice]
l(11) = Me![Weight]
s1 = Me![Com]


With rs1
.MoveLast
l(1) = (rs1![SupplyID] + 1)
.AddNew
rs1![SupplyID] = l(1)
rs1![CustomerID] = l(4)
rs1![DocketNo] = t(2)
rs1![SupplyDate] = d1
rs1![AuctionID] = l(2)
rs1![ProductID] = l(5)
rs1![ColourID] = t(3)
rs1![Grade] = t(4)
rs1![Size] = t(5)
rs1![UnitType] = t(6)
rs1![UnitsLots] = l(6)
rs1![Lots] = l(7)
rs1![MinBuy] = l(8)
rs1![MinPrice] = l(9)
rs1![Weight] = l(11)
rs1![Com] = s1
.Update
.MoveLast
Me![SupplyID] = rs1![SupplyID]
.Close
End With

With rs2
.AddNew
rs2![SupplyID] = l(1)
rs2![AuctionID] = l(2)
rs2![ClockNo] = 1
rs2![Trolly] = l(3)
rs2![Location] = t(1)
rs2![CustomerID] = l(4)
rs2![SupplyDate] = d1
rs2![ProductID] = l(5)
rs2![ColourID] = t(3)
rs2![Grade] = t(4)
rs2![Size] = t(5)
rs2![UnitType] = t(6)
rs2![UnitsLots] = l(6)
rs2![Lots] = l(7)
rs2![MinBuy] = l(8)
rs2![MinPrice] = l(9)
rs2![StartPrice] = l(10)
rs2![Weight] = l(11)
.Update
.Close
End With

Me![Copy].SetFocus
Me![Save].Visible = False

Dim stDocName As String

stDocName = "R_OfferSlid"
DoCmd.OpenReport stDocName, acNormal



Exit_Save_Click:
Exit Sub

Err_Save_Click:
MsgBox Err.Description
Resume Exit_Save_Click

End Sub
Click to expand...
Click to expand...
 
A

Albert D. Kallal

Len said:
Sorry "This is the wrong way to post"
What do you mean..
Click to expand...

You are making posts all over the place. How are people here who volunteer
their time supposed to keep track of who answered you question, and where
the answers are? It is consider very rude to just throw a question without
any respect of the limited and valuable time that people spend here.

Further, posting a whole bunch of code as you did is also rather rude. What
do you think we are here?

You have to understand that people here are willing to help you (judging by
the number of posts you have, you also make your self a bit of mess....as we
now all have to look at each of your posts..and see if anyone answer (or
we will be further wasting our time). It is considered rude to multi-post as
you did.

You also seem to be wondering why no one is answering your question?

I think people here are willing give you some hints on how to cook food.
However, I have no desire to purchase your food, cook you a full meal, clear
up you DIRTY dishes after and send you on your merry way.

Why would I do that if you are making ZERO efforts on your part?

So, posting a whole bunch of lines of code and asking what is wrong without
at least give some info as to what your error message is, what line of code
it stops on etc means you are just throwing something out at a
world..and say..please fix my problem as I am even too lazy to
even mention what the error message is! Why should we do all the work for
you? Why are you so disrespectful of the time and efforts that people give
out for free here? Can not make some efforts on your part?

No one here owes you answer...but if you want to get a answer
you should at least have some respect for the people here.

99% of the people here are NOT paid to give answers, and do so
freely from the good ness of their heart and their limited spare time.

If you can not show one bit of respect for the free advice you can get here
I
suggest you pick up the yellow pages and hire a good consultant. There are
many available in the $75 to $120 per hour range that will help you.

If you are willing to make some efforts on your part..then this newsgroup
can really help you. But, YOU HAVE to be willing to make some efforts here.
This whole system works when all people involved are willing to make an
honest effort.

If you are new newsgroups, and how volunteering of your time works, then I
apologize if the above sounds harsh.

I am not mad at you at all, but I just want you to understand how this whole
system of community sprit, and helping others help themselves works.

So, lets try this whole thing again:

First, you need to provide more information. You can't just say your car is
broken..and expect anyone to be able to help you.

How about some hits, or ideas, even one small tid bit of help from you as to
what does not work? Throw us dogs at least some part of bone to work
on here. Make SOME effort on your part here to help.

Out of all those lines you posted...do you get a error message, does the
code stop on a particular line? (in fact..why not post just the one line
code that stopped?).

Which line of code don't work? (why make us look through all those lines of
code when YOU have information as to what line of code it stops on?

Why hide, and keep the error messages secret from us..and then make us guess
what your error is?

Also, can you edit those tables in the direct table view (ie: just open the
tables...can you modify them using the access UI?) There is no use trying to
get code to modify data if you can't edit the data via the UI.

Lets work on a solution to your problem...as many of us are here to help...
 
L

Len

Well>>>>>>>>>
I am new in the groups..
Didn't know were to post it as there are so many groups on the news server.
I posted all the code as I thought it would made more seens
..
"you should at least have some respect for the people here"
If I was Dis-respectfull I'm sorry. Was not intended and didn't know one
could offend someone so much so fast. But it is done and up there.

The error
The copy of the code has an incomplet 1st line.
dim db as Database

This were it stops.
Compile error
User-Defined type not defined.

I had it all working when it was an Access Database
I have put together a MySQL database Linked all the tables and trying to
make Access work on it.
I can read from the tables and write to them using access Queries but not
through VB code.

MySQL is all new to me, I have always just been using Access and can get
around most things.

Len
 
D

Dirk Goldgar

Len said:
Well>>>>>>>>>
I am new in the groups..
Didn't know were to post it as there are so many groups on the news
server. I posted all the code as I thought it would made more seens
.
"you should at least have some respect for the people here"
If I was Dis-respectfull I'm sorry. Was not intended and didn't know
one could offend someone so much so fast. But it is done and up there.

The error
The copy of the code has an incomplet 1st line.
dim db as Database

This were it stops.
Compile error
User-Defined type not defined.

I had it all working when it was an Access Database
I have put together a MySQL database Linked all the tables and trying
to make Access work on it.
I can read from the tables and write to them using access Queries but
not through VB code.

MySQL is all new to me, I have always just been using Access and can
get around most things.

Len
Click to expand...

My guess is that this code was copied from an Access 97 database into an
Access 2000 or 2002 database. Is that guess correct? If so, and if you
are working with the MySQL tables as linked tables in your Access .mdb
file, then you don't need to change the code much at all. You should
make your declarations for the Database and Recordset objects like this:

Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset

And you should also, while in the VB editor, click Tools ->
References..., locate the reference named "Microsoft DAO 3.6 Object
Library" in the list, and put a check mark in the box next to it. I
think that will probably solve your problem.

Access 2000 and 2002, by default, don't include the reference to the DAO
library that you need to use these objects. They also include by
default a reference to the ADO (ActiveX Data Objects) library, which
also defines a *different* recordset object -- one which you don't want
to use. So you have to tell Access when you declare them that these
objects are supposed to be the DAO, not ADO, versions.

If my guess was wrong, and something else is wrong, please reply in this
thread.
 
T

TC

Hi Len

Not sure what justifies the blast you got in response to asking what was
meant.

I haven't seen your other posts (I've been off for a few days), but maybe
you are "multi-posting"? That is where you post the same question
seperately, to different newsgroups. Multi-posting is bad, & wastes
everyones' time.

The preferred method is "cross-posting". That is where you post the question
*once*, placing *all* of the relevant newsgroup names in the Newsgroups:
line of that one post. Cross-posting is fine, when done in moderation, &
restricted to relevant newsgroups only.

Search the web for the term "netiquette" for more information on those &
other relevant issues.

HTH,
TC


Len said:
Sorry "This is the wrong way to post"
What do you mean..

Len


GVaught said:
Answered in another area. This is the wrong way to post.

Len said:
The funtion below worked when it was running on the Access Database but
fails now.
I have linked the tables from the MySQL Database using the same names as
they were before.
The form above this code can see the information in the tables this
Click to expand...
code
is
under the save button.

Can anyone help me in changeing this to work with the MySQL database.

Private Sub Save_Click()
On Error GoTo Err_Save_Click

Dim db As
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim l(13) As Long
Dim s1 As Single
Dim t(7) As String
Dim d1 As Date


Set db = CurrentDb()
Set rs1 = OpenRecordset("Supply", dbOpenDynaset)
Set rs2 = OpenRecordset("Invertory", dbOpenDynaset)

d1 = Me![Date]
l(2) = Me![AuctionID]
l(3) = Me![TrollyNo]
t(1) = Me![Location]
l(4) = Me![CustomerID]
t(2) = Me![DocketNo]
l(5) = Me![ProductID]
t(3) = Me![ColourID]
t(4) = Me![g1] & Me![g2] & Me![g3]
t(5) = Me![Size] & Me![Combo50]
t(6) = Me![UnitType]
l(6) = Me![UnitLots]
l(7) = Me![Lots]
l(8) = Me![MinBuy]
l(9) = Me![MinPrice]
l(10) = Me![StartPrice]
l(11) = Me![Weight]
s1 = Me![Com]


With rs1
.MoveLast
l(1) = (rs1![SupplyID] + 1)
.AddNew
rs1![SupplyID] = l(1)
rs1![CustomerID] = l(4)
rs1![DocketNo] = t(2)
rs1![SupplyDate] = d1
rs1![AuctionID] = l(2)
rs1![ProductID] = l(5)
rs1![ColourID] = t(3)
rs1![Grade] = t(4)
rs1![Size] = t(5)
rs1![UnitType] = t(6)
rs1![UnitsLots] = l(6)
rs1![Lots] = l(7)
rs1![MinBuy] = l(8)
rs1![MinPrice] = l(9)
rs1![Weight] = l(11)
rs1![Com] = s1
.Update
.MoveLast
Me![SupplyID] = rs1![SupplyID]
.Close
End With

With rs2
.AddNew
rs2![SupplyID] = l(1)
rs2![AuctionID] = l(2)
rs2![ClockNo] = 1
rs2![Trolly] = l(3)
rs2![Location] = t(1)
rs2![CustomerID] = l(4)
rs2![SupplyDate] = d1
rs2![ProductID] = l(5)
rs2![ColourID] = t(3)
rs2![Grade] = t(4)
rs2![Size] = t(5)
rs2![UnitType] = t(6)
rs2![UnitsLots] = l(6)
rs2![Lots] = l(7)
rs2![MinBuy] = l(8)
rs2![MinPrice] = l(9)
rs2![StartPrice] = l(10)
rs2![Weight] = l(11)
.Update
.Close
End With

Me![Copy].SetFocus
Me![Save].Visible = False

Dim stDocName As String

stDocName = "R_OfferSlid"
DoCmd.OpenReport stDocName, acNormal



Exit_Save_Click:
Exit Sub

Err_Save_Click:
MsgBox Err.Description
Resume Exit_Save_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Conversion of excel vba code to access vba 3
Export to Excel: 2nd, 3rd, etc excel files not opening up 7

Top